Consolidating Data

[Marc Enzi]

I am trying to Consolidate Data In accordance with the INStructions in the Mr. Excel Book I purchased and I am getting nowhere.

Here is what I have so Far
List1 List2
Eric 1 John 2
Nancy 1 Stephen 2
John 1 Eric 2
Lee 1 Nancy 2
Ana 1 Bill 2
Stephen 1 Bob 2

Next it tells me to make a Column Labelled ListNumber
so and place List 2 under List 1 here is what I have

List1 ListNumber
Eric 1
Nancy 1
John 1
Lee 1
Ana 1
Stephen 1
John 2
Stephen 2
Eric 2
Nancy 2
Bill 2
Bob 2

Now it tells me:
1. Press Ctrl+*, Ctrl+F3 and define a Name for the List I use KidNames which Excel defined as =Sheet7!$A$1:$B$13
2. From the DATA menu, select CONSOLIDATE
3. In the Reference Box, press F3 and paste the Name you defined for the list.
4. Click Add, select both Use labels in checkboxes and click OK.

Then I get an error that says SOURCE REFERENCE OVERLAPS DESTINATION AREA.

Can anyone please help I am blue in te face with this. I actually two large lists that I either have to painstakingly sort through or use Excel.....

Thanks

Marc Enzi
832-391-3400
menzi@earthlink.net

 

[sweater_vests_rock]

marc.

make sure you are selecting only one cell, and that one cell should not be in your list range. for example, see the htmlmaker image below. i clicked on the cell c1, then used the steps you indicated (skipped ctrl+* as that was causing an error)

ben.
(ignore the &nspb stuff -- that is simply a blank cell)

book1
	A	B	C	D
1	List1	ListNumber		ListNumber
2	Eric	1	Eric	3
3	Nancy	1	Nancy	3
4	John	1	John	3
5	Lee	1	Lee	1
6	Ana	1	Ana	1
7	Stephen	1	Stephen	3
8	John	2	Bill	2
9	Stephen	2	Bob	2
10	Eric	2
11	Nancy	2
12	Bill	2
13	Bob	2
Sheet1

 

[Marc Enzi]

Thanks for the help-I am still a bit confused

Okay, please help me to understand what ranges I need to select at each step.

Thanks.

Marc

 

[sweater_vests_rock]

marc.

sure thing!

step 1: select the entire list1 and listnumber range (include headers)

step 2: Hit ctrl+F3, select a name for this range, click "Add" and then "Ok"

step 3: Select the first blank cell in your header row (row 1, column c in the above example)

step 4: On the toolbar menu, goto Data->Consolidate

step 5: In the references box, type the name you selected in step 2 and click "Add"

step 6: check both checkboxes at the lower left corner of the form

step 7: click "Ok"

hope that helps. ben.