Compare duplicate items and return a value

[tigabalm]

I am looking to see if there is a way to find duplicate items in a list and return a value, if a duplicate exists.

Basically I have 19 buildings each with a separate bill of quantities. I'm looking to compare and compile a comprehensive list. So that duplicate items in different buildings can be compared side by side.

I have attached a sample of what I am trying to do and I would greatly appreciate any help or ideas.

Thanks

[teylyn]

Hi,

I'm not sure where the duplicates come in. As far as I see it, you want to list the values from the different sheets in the Overview sheet. That can be done with a Vlookup.

B3 =IF(ISNA(MATCH(A3,'Building 1'!$A$2:$A$5,0)),"",VLOOKUP(A3,'Building 1'!$A$2:$B$5,2,FALSE))
C3 =IF(ISNA(MATCH(A3,'Building 2'!$A$2:$A$5,0)),"",VLOOKUP(A3,'Building 2'!$A$2:$B$5,2,FALSE))
D3 =IF(ISNA(MATCH(A3,'Building 3'!$A$2:$A$5,0)),"",VLOOKUP(A3,'Building 3'!$A$2:$B$5,2,FALSE))

copy down.

hth

[tigabalm]

teylyn, thank you so much! Thats basically 90% of what I need. The only thing is, is there a a way to populate the "items" column in the overview sheet with the values in the building sheets.

I would like to compile every item from every building, but if i copy paste, i'm obviously going to get "apples" three times, "oranges" twice, etc.

Thanks again!

[teylyn]

tigabalm, if it's a one-off solution you're after I'd suggest

- copy and paste the content on the populated cells in columns A into a blank column on some other sheet
- use advanced filter to filter out unique values
- copy the unique values and paste into the Overview sheet.

I would set up a "master list" of item values on the Overview sheet and use data validation in column A of the building sheet to ensure that only the values on the master list can be selected.

If you need help with that, pipe up.

cheers

[tigabalm]

teylyn, thanks for the idea. been messing around with the data validation stuff, but its not quite working out.... I made a master list of items, but data validation isn't sorting them. So I tried using cell formatting to highlight cells with the same value. But running into problems on that front too.

Any ideas?

[teylyn]

Data validation will not do anything re sorting. It will take the list as is. If you need the data validation list sorted, you will have to prepare it such.

again, my approach would be

- copy all exsting values into one column,
- use Advanced Filter to create a list of unique values, i.e. without duplicates
- copy and paste the list of unique values into the Overview sheet
- sort there, if you like
- create a range name for the list on the Overview sheet
- use that range name for the source of the data validation list on the other sheets

[tigabalm]

okay almost there... so I have the master list setup (thank you again), but of course I've run into another problem. There are certain items that are specifics of a class type.

IE:

1.1 Granite floors
- 25 mm
- 30 mm

2.1 Marble floors
-25 mm
-30 mm
-35 mm

So i'm thinking the original formula you provided will just pull the first "25 mm" value, instead of differentiating between the granite and marble floors...

[teylyn]

Well, for both the data validation as well as the Vlookup scenario, it will be a lot easier if both list lookup table contains only unique values like

1.1 Granite floors - 25 mm
1.1 Granite floors - 30 mm

[tigabalm]

Teylyn, thanks for all your help so far. I ended up changing all spec item names to match their corresponding class, per your suggestion and successfully, populated my table! Success!

just one, slight speed bump left, when I try summing the values across the buildings (horizontally), some of the rows will correctly, calculate the sum, but others will return "0" when in fact there are values in that row. I assume, its an issue with text vs numericals, but I don't understand why sometimes it sums and other times not.

i've tried
=sum(A1:Z1)
=sum(A1,B1,C1,D1) etc
=(A1+B1+C1+D1) etc

all have the same effect.

thanks again for all your help!

[teylyn]

You'd need to post a data sample to enable checking of the data type.

compare

=sum(A1,B1,C1,D1)
vs
=sum(A1*1,B1*1,C1*1,D1*1)

if the latter gives you a correct sum, then some of your numbers are stored as text and you need to clean up your data.

[tigabalm]

Got it! turns out some of the values were entered as text, so i used the 1* paste special technique and converted all the numbers (Still a pain to repeat over 17 sheets, but much better than going in and retyping all the numbers manually).

Thanks Teylyn for all your help!

[teylyn]

Great feeling when it all falls into place, isn't it?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save