Combining two columns into one

[aabbasi]

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

[Mark W.]

Are there any differences between the Names on Sheet1 and Sheet2? If not, will that always be the case?

[aabbasi]

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"

[Mark W.]

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/viewtop...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 ]

[aabbasi]

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?

[Mark W.]

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?

[aabbasi]

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.

[Mark W.]

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.