Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Combining two columns into one

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Chicago, USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Chicago, USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Chicago, USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Chicago, USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •