Combining two columns into one

aabbasi

Board Regular
Joined
Mar 4, 2002
Messages
188
In my column A, I have "Names", in B it has "March Sales Results".

On sheet 2, I have "Names", in B column "April Sales".

I like to have one Column of "Names" in column A, in column B "Mar Sales, in column C "apr Sales".

Please advise, how to do it. Thank you
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Are there any differences between the Names on Sheet1 and Sheet2? If not, will that always be the case?
 
Upvote 0
Thank you for your interest.

Yes, the list of Names will have some different names eg: First List of Name may have " John, Gene, Bob" and the second list of names may be " Bill, John, Amy"
 
Upvote 0
On 2002-05-07 15:16, aabbasi wrote:
Thank you for your interest.

Yes, the list of Names will have some different names eg: First List of Name may have " John, Gene, Bob" and the second list of names may be " Bill, John, Amy"

Leveraging the technique described at http://www.mrexcel.com/board/viewtopic.php?topic=7549&forum=2&3...

...this SQL...

SELECT Table1.Names, Table2.`Mar Sales`, Table1.`April Sales`
FROM {oj Table1 Table1 LEFT OUTER JOIN Table2 Table2 ON Table1.Names = Table2.Names}
UNION
SELECT Table2.Names, Table2.`Mar Sales`, Table1.`April Sales`
FROM {oj Table2 Table2 LEFT OUTER JOIN Table1 Table1 ON Table1.Names = Table2.Names}

...produces...

{"Names","Mar Sales","April Sales"
;"Amy",25,0
;"Bill",5,0
;"Bob",0,30
;"Gene",0,20
;"John",15,10}

...from...

{"Names","April Sales"
;"John",10
;"Gene",20
;"Bob",30}

...and...

{"Names","Mar Sales"
;"Bill",5
;"John",15
;"Amy",25}
This message was edited by Mark W. on 2002-05-07 15:35
 
Upvote 0
Thank you for your advice..

When I try to run SQL, it gives an error message that

Missiing LEFT or RIGHT before Sheet1$

Am I doing something wrong?
 
Upvote 0
I'm not quite sure what you mean by "...When I try to run SQL...". Are you creating named ranges for your 2 lists (Table1 and Table2 in my example) and using the Data | Get External Data | Create New Query... menu command?
 
Upvote 0
Thank you once again..

I tried again your SQL, it WORKED PERFECT!!!

Just out of Curosity, if I have 6 tables instead of 2 tables and each tables have 10 columns. How I can do? (The concept is still the same to Combine Name columns into one Column)

Thank you.
 
Upvote 0
On 2002-05-08 08:42, aabbasi wrote:
Thank you once again..

I tried again your SQL, it WORKED PERFECT!!!

Just out of Curosity, if I have 6 tables instead of 2 tables and each tables have 10 columns. How I can do? (The concept is still the same to Combine Name columns into one Column)

Thank you.

I'd have to know more about the nature of your 10 columns, but assuming that they represented the same values you could 'UNION ALL' the tables as shown below...

SELECT T1.Col1, T1.Col2, T1.Col3...
FROM Table1 T1
UNION ALL
SELECT T2.Col1, T2.Col2, T2.Col3...
FROM Table2 T2
UNION ALL
...
UNION ALL
SELECT T6.Col1, T6.Col2, T6.Col3...
FROM Table6 T6;

...And, then source a PivotTable from the resultant data range.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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