• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting and matching of Data Problem

H

Hamish

Member
I have a need to find a set of data from an imported data source, then find all the data that is the same and sum the totals so I don't have to sift through the data looking for similar items.

Example:

Column 1 - Description "Pine Architrave" Column 2 - "10.32" (Qty.) Column 3 - Description "Architrave Pine (Window) to Bed 3 (lm).

I need the function to determine that it is looking for "lm" or "m2" or "m3" and place is next to a summed total of numbers that match the description. eg "bed" as all bedroom architraves are the same.

For Some reason I am unable to upload the file so I have pasted the data instead.

10.32
Architrave Pine (Window) to Bed 3 (lm) Finds this description ( and all others relating to bedrooms, recognises it is a "Window in a Bedroom" and sums the totals with "lm" next to it.
6.36 Architrave Pine (Window) to Kids Area/bed 4 (lm)
6.36 Architrave Pine (Window) to Bed 2 (lm)
5.30 Architrave Pine (Window) to Bath (lm) Finished cell will read; Architrave Pine (Window) to Bed 1,2,3 55 lm
6.36 Architrave Pine (Window) to Dining (lm)
4.82 Architrave Pine (Window) to Kitchen (lm)
4.07 Architrave Pine (Window) to Ens (lm)
12.38 Architrave Pine (Window) to Principal Suite (lm)
3.28 Architrave Pine (Window) to Wc (lm)
5.65 Architrave Pine (Door) to Ldry (lm)
5.15 Architrave Pine (Door) between Porch and Entry (lm)
6.61 Architrave Pine (Door) between Alfresco and Living (lm)
5.13 Architrave Pine (Door) to Garage (lm)
9.60 Architrave Pine (Door) between Bath and Passage (lm)
9.80 Architrave Pine (Door) between Ldry and Passage (lm)
10.64 Architrave Pine (Door) between Passage and Linen (lm)
9.80 Architrave Pine (Door) between Bed 3 and Passage (lm)
9.80 Architrave Pine (Door) between Bed 2 and Passage (lm)
11.00 Architrave Pine (Door) between Bed 2 and Robe (lm)
9.60 Architrave Pine (Door) between Wc and Passage (lm)
12.56 Architrave Pine (Door) between Kitchen and Pantry (lm)
9.80 Architrave Pine (Door) between Principal Suite and Entry (lm)
9.80 Architrave Pine (Door) between Garage and Passage (lm)
10.69 Architrave Pine (Door) between Bed 3 and Robe (lm)
9.60 Architrave Pine (Door) between Principal Suite and Ens (lm)
10.69 Architrave Pine (Door) between Kids Area/bed 4 and Robe (lm)
1.00 Bathtub Vivas 1675 to Bath on Ground Floor (count)
178.38 Concrete Slab (m2)
9.72 Cornice to Kitchen Ground Floor (lm)
11.94 Cornice to Kids area/bed 4 Ground Floor (lm)
3.86 Cornice to Linen Ground Floor (lm)
7.62 Cornice to Ldry Ground Floor (lm)
12.68 Cornice to Robe Ground Floor (lm)
5.92 Cornice to Pantry Ground Floor (lm)
23.00 Cornice to Garage Ground Floor (lm)
9.34 Cornice to Bath Ground Floor (lm)
5.40 Cornice to Wc Ground Floor (lm)
7.40 Cornice to W.i.r Ground Floor (lm)
14.46 Cornice to Principal suite Ground Floor (lm)
13.26 Cornice to Entry Ground Floor (lm)
5.18 Cornice to Dining Ground Floor (lm)
11.74 Cornice to Bed 2 Ground Floor (lm)
13.60 Cornice to Living Ground Floor (lm)
8.50 Cornice to Ens Ground Floor (lm)
12.04 Cornice to Bed 3 Ground Floor (lm)
19.82 Cornice to Passage Ground Floor (lm)
2.00 Door Internal Cupboard Sliding Plain 2.040 x 1.265 x 2 between Bed 3 and Robe (count) between Bed 3 and Robe
1.00 Door Internal Cupboard Sliding Plain 2.040 x 1.420 x 2 between Bed 2 and Robe (count) between Bed 2 and Robe
1.00 Aluminium Sliding Door 2.100 x 1.450 Clear Glazed to Ldry (count)
1.00 Aluminium Sliding Door 2.100 x 2.410 Clear Glazed between Alfresco and Living (count)
1.00 Timber Entry Door Frame 2.130 x 0.865 Clear Glazed 0 Sidelight to Garage (count)
1.00 Timber Entry Door Frame 2.140 x 0.865 Clear Glazed 0 Sidelight between Porch and Entry (count)
1.00 Internal Door Frame Pine Double 1.240 Hinged between Passage and Linen (count)
1.00 Internal Door Frame Pine Double 1.420 Sliding between Bed 2 and Robe (count)
1.00 Internal Door Frame Pine Double 1.265 Sliding between Bed 3 and Robe (count)
1.00 Internal Door Frame Pine Double 1.265 Sliding between Kids Area/bed 4 and Robe (count)
1.00 Internal Door Frame Pine Single 0.720 Hinged between Bath and Passage (count)
1.00 Internal Door Frame Pine Single 0.820 Hinged between Ldry and Passage (count)
 
Hi Hamish ,

I am not able to understand your problem ; can you retry uploading your file , so that at least your data layout is clear ?

You talk of lm , m2 , m3 , but your data shows only lm and count ; hopefully , if you can upload your file , it will have a greater variety of data , and it would be nice if you could work out the desired output in at least 2 or 3 cases which are different from each other , so that what you want is clear.

Narayan
 
to clarify... "count" is the unit of measure that I need to recognise as well as "lm" (linear metres) "m2" (metres squared) etc.
 
Hi Narayan,

The 55lm was just an example. I need to recognise all numbers relating to "lm" and sum as per their descriptions. Same principle for "count", "m2", "m3" etc.
 
Hi Narayan,

Definitely on the right track. I still need to seperate the items out. Eg. "brick sill" in lm is different to "architrave pine" in lm.

Is it possible for all the descriptions to roll in to one? Eg. all the descriptions matching "Architrave Pine" are rolled into one description such as; "Architrave Pine to Bed 1,2,3,Kids Area". Then sum the total?
 
Hi Hamish ,

If you have seen the formula in column G , it is using the criteria cells G2 , H2 and I2 , which at present contain the text Window , Bed and (lm).

If you wish to add more terms , you can extend the range to J2 , K2 ,... and enter the search terms in those cells ; thereafter , change the formula to include these added terms.

If you want the total of Architrave , then remove the filter on column G , and put in one on Column D , selecting ArchitravePine.

You can easily add more power and functionality to this search system by using VBA , but see how far you can go without , and if you feel that you need more functionality , post back with details.

Narayan
 
You must log in or register to reply here.
Back
Top