Consolidating Data

Marc Enzi

Board Regular
Joined
Dec 13, 2004
Messages
92
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
ABCD
1List1ListNumberListNumber
2Eric1Eric3
3Nancy1Nancy3
4John1John3
5Lee1Lee1
6Ana1Ana1
7Stephen1Stephen3
8John2Bill2
9Stephen2Bob2
10Eric2
11Nancy2
12Bill2
13Bob2
Sheet1
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,452
Messages
6,101,939
Members
447,764
Latest member
gopalgriffith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top