Trying to consolidate / reconcile two lists

[silveroo81]

So I have two lists (list A and B). Each with their project codes (key).

Each project code has a building count. My problem is that I have two such lists and the building count differs.

I want Excel to present: for list A, there are X building count, vs list B, there are Y building count. And then I'll make a decision on which I choose.

How can I most easily reconcile the data in excel? pivot table?

File is at: v.subs.xls

thank you

[FDibbins]

Hi and welcome to the forum

lets see if this is heading in the right direction. On sheet1, in any column, use this, copied down...

=COUNTIF('B List'!I:I,MID(A19,3,99))
this will give you a count of how many times the Project Code on sheet1 appears on sheet2

If we are heading in the right direction, we can build on this, so let me know

[outofthehat]

It would help if your 2 lists matched... I assume that list A has an extra 03 on the front.

I tend to do this sort of thing with a quick VLOOKUP. Take a look at the attached, which uses

Please Login or Register  to view this content.

to check each code in the A List (with the _03 stripped off) against the B list and look up the number of buildings.

I've also done a simple check to see if there are any in list B not in A, with the MATCH function,

Please Login or Register  to view this content.

, so I can now filter on 'Not in A'.

NB I had to remove all merged cells in List A column A (these are really annoying when trying to do analysis like the above - I tend to avoid merged cells almost at all costs!).