• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in CONCATENATE formula...

Jawaharp

New Member
Hi Friends,

Please help in one CONCATENATE formula

ex - Parent Id has multiple child id should be concatenate with ","

Sample file has enclosed for your reference...

Thanks
Jawahar Prem
 

Attachments

  • test.xlsb
    8.8 KB · Views: 12
1. Bring table into Power Query and fill down all nulls
2. Close and Load to table and make sure Data Model is checked
3. Open Power Pivot and create Pivot Table from Table
4. Create measure as follows Children:=CONCATENATEX(Table1,Table1[Child_ID],", ") using DAX
5. Pivot Table Parent ID in Rows, Children(new measure) in Values

Data Range
D
E
1
Parent_ID​
Children​
2
6235002350​
8606530653​
3
6235035581​
7560863015​
4
6235039872​
7034039872​
5
6235050502​
8086700669, 8086700669, 8592931619, 8592931619, 9745271965, 9745271965​
6
6235050550​
9946164159, 9946164159​
7
6235058555​
8593851355​
8
6235059900​
9846202247, 9846202247​
9
6235060606​
7025015744, 7025653034, 7592993355, 8086225362, 8086555055, 8086600050, 8589901111, 8592871049, 8593857878, 8593877878, 8593887878, 9048661666​
 

Attachments

  • test.xlsb
    127.5 KB · Views: 3
There's a not very good formula, in C2, copied down:
Code:
=IF(A2="","",TEXTJOIN(",",TRUE,A2,B2,FILTER(B3:B$27,ROW(A3:A$27)<MIN(IF(A3:A$27<>"",ROW(A3:A$27))),"")))

You realise you've got duplicates in column A? Two each of 6235050502, 6235050550 & 6235059900.
 
Last edited:
This Power Query will give you the same results as yours (ignores that there are duplicates), but closes up the spaces:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilledDown = Table.FillDown(Source,{"Parent_ID"}),
    GroupedRows = Table.Group(FilledDown, {"Parent_ID"}, {{"grp", each _, type table [Parent_ID=number, Child_ID=number]}},GroupKind.Local),
    InvokeCustomFunction = Table.AddColumn(GroupedRows, "Result", each List.Combine({{Table.FirstValue([grp])},[grp][Child_ID]})),
    ExtractValues = Table.TransformColumns(InvokeCustomFunction, {"Result", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Result = Table.SelectColumns(ExtractValues,{"Result"})
in
    Result
74537
 
Last edited:
Hi Sir,

Thank You for your time,

But I have more than 2 Lakhs ID filling down the null is very hard..

its any other method pleas help..

Thank yuu.
Jawahar


1. Bring table into Power Query and fill down all nulls
2. Close and Load to table and make sure Data Model is checked
3. Open Power Pivot and create Pivot Table from Table
4. Create measure as follows Children:=CONCATENATEX(Table1,Table1[Child_ID],", ") using DAX
5. Pivot Table Parent ID in Rows, Children(new measure) in Values

Data Range
D
E
1
Parent_ID​
Children​
2
6235002350​
8606530653​
3
6235035581​
7560863015​
4
6235039872​
7034039872​
5
6235050502​
8086700669, 8086700669, 8592931619, 8592931619, 9745271965, 9745271965​
6
6235050550​
9946164159, 9946164159​
7
6235058555​
8593851355​
8
6235059900​
9846202247, 9846202247​
9
6235060606​
7025015744, 7025653034, 7592993355, 8086225362, 8086555055, 8086600050, 8589901111, 8592871049, 8593857878, 8593877878, 8593887878, 9048661666​
 
Hi Sir,

thanks for your time, there is not duplicate in Column A

while applying this formula there is error shown in "filter"

There's a not very good formula, in C2, copied down:
Code:
=IF(A2="","",TEXTJOIN(",",TRUE,A2,B2,FILTER(B3:B$27,ROW(A3:A$27)<MIN(IF(A3:A$27<>"",ROW(A3:A$27))),"")))

You realise you've got duplicates in column A? Two each of 6235050502, 6235050550 & 6235059900.
 
while applying this formula there is error shown in "filter"

Filter is Office 365 function, if your formula return error that mean you don't have Office 365.

Here is another option, which can suit with all Excel versions.

Since as your replied, there is no duplicate in Column A, data in range A23:A27 changed to 6235059901, 6235050503, 6235050551

A) "Parent ID" Column

1] In "Parent_ID" column fill all blank cells with value from above
  • Select A2:A27
  • Press F5 or Ctrl+G
  • Click Special...
  • Select Blanks, then click OK
  • Enter the formula =A5 and press Ctrl+Enter
2] Masking the duplicate value in A2:A27 with white front
  • Select A2:A27
  • Click Conditional Format
  • Select New Rule
  • In the rule b enter :=COUNTIF($A$1:$A2,$A2)>1
  • and choose Font in white color
B) "Result" Column

3] In C2, formula copied down :

=IF(COUNTIF($A$2:$A2,A2)=1,A2&", ","")&B2&IFERROR(", "&VLOOKUP(A2,A3:C$28,3,),)

4] Masking the C2:C7 with correspondence duplicate in A2:A27 with white front
  • Select C2:C27
  • Click Conditional Format
  • Select New Rule
  • In the rule b enter :=COUNTIF($A$1:$A2,$A2)>1
  • and choose Font in white color
74547
 

Attachments

  • CONCATENATE.xlsx
    12.3 KB · Views: 5
Filter is Office 365 function, if your formula return error that mean you don't have Office 365.

Here is another option, which can suit with all Excel versions.

Since as your replied, there is no duplicate in Column A, data in range A23:A27 changed to 6235059901, 6235050503, 6235050551

A) "Parent ID" Column

1] In "Parent_ID" column fill all blank cells with value from above
  • Select A2:A27
  • Press F5 or Ctrl+G
  • Click Special...
  • Select Blanks, then click OK
  • Enter the formula =A5 and press Ctrl+Enter
2] Masking the duplicate value in A2:A27 with white front
  • Select A2:A27
  • Click Conditional Format
  • Select New Rule
  • In the rule b enter :=COUNTIF($A$1:$A2,$A2)>1
  • and choose Font in white color
B) "Result" Column

3] In C2, formula copied down :

=IF(COUNTIF($A$2:$A2,A2)=1,A2&", ","")&B2&IFERROR(", "&VLOOKUP(A2,A3:C$28,3,),)

4] Masking the C2:C7 with correspondence duplicate in A2:A27 with white front
  • Select C2:C27
  • Click Conditional Format
  • Select New Rule
  • In the rule b enter :=COUNTIF($A$1:$A2,$A2)>1
  • and choose Font in white color
View attachment 74547

Hi Sir,

Thank You Ver Much...
 
Back
Top