Mr Excel On Excel Book - Comparing Lists page 308

[paalley]

In The Mr Excel On Excel book page 308 it has a section on comparing lists. I have 2 questions:
It says add column b but add it to what?
It also says to cut cells C2:D7 but it never says to put anything in Column C so I don't know what it is supposed to copy.

The data consolidation technique allows you to compare lists quic-easily.
In Chapter 8, Summing and Counting, refer to the technics comparing lists by using the COUNTIF formula.
With the consolidation technique, you can compare two or more lists without using a formula.
Look at the figure: List 1 is in column A, and List 2 is in column B.
1. Add Column B. In Cell B1, type "List number".
2. In Cells B2:B7, enter the number 1.
3. In Cells D2:D7, enter the number 2.
4. Cut Cells C2:D7 and paste them into Cell A8.
result is shown in figure to the right.

Select cell A1. Press Ctrl+*, press Ctrl+F3, and define a name for the list.
From the Data menu, select Consolidate.
In the Reference box, press F3 and paste the Name you defined for the list.
Click Add, select both Use labels in checkboxes, and click OK.

 

[shades]

Howdy. Re, p. 308, I think the assumption is that you should insert a column between the two listed in the figure. Then in B1, put "List Number", and in B2:B7 put the number 1. With the inserted column, now the other set of names (which had been in col B) are now in Col C. Therefore put 2 into cells D2:D7.

Then copy C2:D7 and paste into A8.

 

[paalley]

Thanks. I tried that and thought it was going to work but I am getting an error but at least I am on the right track now. I need some way to compare 2 list of numbers to see if the numbers are on both lists. I will have several uses for this if I can get it to work. Thanks again.

 

[MrExcel]

1) You have List1 in A and List2 in B

2) Insert a new column B. This moves the old column B to C.

3) In the new column B, enter 1’s

4) In column D, enter 2’s

5) Now, combine the lists into one list. Cut the items from C:D over to A:B, just below List1. Don't include any heading from C when you cut the data.

When you do Data – Consolidate, it adds up the List # values from column B and shows you one line for each element in column A.

Anything with a “3” was in both lists.
Anything with a “1” was only in the first list
Anything with a “2” was only in the second list.

Note…. If you want to extend this to more lists, the third list should have a List # of 4. The 4th list should have a list # of 8. The fifth list should have a list number of 16 (see – it doubles each time). This ensure that no matter what answer you get in the consolidate step, you can figure out the unique lists.

 

[shades]

Howdy, Bill. I thought that was what I posted.

 

[MrExcel]

Sure... same thing.... The OP had posted the question here and sent it to my e-mail. When his e-mail bounced back, I posted the reply here as well just to point out that I am listening and trying to help...

 

[shades]

Sure... same thing.... The OP had posted the question here and sent it to my e-mail. When his e-mail bounced back, I posted the reply here as well just to point out that I am listening and trying to help...

Just teasing a little on a tough day.

I assumed something like. You have an excellent reputation for being responsive. And it is a good example for all of us to follow.

Thanks.

 

[paalley]

Thanks

Thanks for the replies and the help guys. It worked!

 

[paalley]

Comparing lists

I just thought it worked.....
After I do the consolidation when I click add and select both use label in the checkboxes and click OK I get the following error:

Source reference overlaps destination area

 

[MrExcel]

Try these steps.

The book says to create a named range, but that will complicate the matter, so skip that.

1) You have data as shown on the top of Page 309. This has names in A2:A13, List Number in B2:B13 and headings in A1:B1.

2) Select a blank cell. Make sure that you have a large blank area to the right of this cell. For example, cell D1 should work. There is nothing left in columns D, E, F...

3) From the menu, choose Data - Consolidate.

4) If you have any ranges listed in the All References section, click each one and click Delete.

5) In the Reference selection, enter A1:B13.

6) Ensure both checkboxes in Use Labels In frame are checked.

7) Click OK. You will get the result shown in the bottom figure on Page 309.

I think that Yosi's description of this gets muddled because in his step 2, he fails to tell you to go to a blank section of the worksheet. In fact, it looks like he moved to a completely blank Sheet2 since his results magically appeared in cell A1.