CODEWITHSUNDEEP
excelgoogle-sheets
Random Stuff
Random Stuff

Reputation: 170

How to join two lists on excel or google sheets

I have two lists that i need joined. I need list b2 to join to list a2 within c2.

For example:

enter image description here

Now Ive found many "answers" for this question however they all give me the same wrong result.

The incorrect result is:

enter image description here

Sites giving these incorrect results:

https://www.get-digital-help.com/merge-two-columns-into-one-list-in-excel/

Upvotes: 1

Views: 1261

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34275

It is a bit awkward in lower versions of Excel, but you could try this, adjusting ranges as necessary:

=IF(ROWS(D$1:D1)>COUNTA($A$2:$B$25),"",
IF(ROWS(D$1:D1)<=COUNTA($A$2:$A$25),INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$25)/($A$2:$A$25<>""),ROWS(D$1:D1))),
INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$25)/($B$2:$B$25<>""),ROWS(D$1:D1)-COUNTA($A$2:$A$25)))))

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

In windows Excel O365, you can use:

C2: =FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A2:A10,B2:B10)&"</s></t>","//s")

enter image description here

With an earlier version, but still Excel 2010+, I'd use Power Query aka Get & Transform. This will give the same result as the formulas above. You'll need to refresh the query if the data changes.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        tbl1 =  Table.RemoveColumns(Source,"List 2"),
        tbl2 = Table.RemoveColumns(Source,"List 1"),
    #"Renamed Columns" = Table.RenameColumns(tbl2,{{"List 2", "List 1"}}),
    combTbl = Table.Combine({tbl1,#"Renamed Columns"}),
    #"Removed Blank Rows" = Table.SelectRows(combTbl, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

Upvotes: 1

marikamitsos
marikamitsos

Reputation: 10573

You can use a query formula for that.

=QUERY({U3:U7;V3:V11},"where Col1<>'' ")

enter image description here

Please read more about QUERY

Upvotes: 2

Related Questions