Concatenate names in excel up to a specific range every time
- Thread starter Amit Rana
- Start date
AlanSidman
Well-Known Member
It would be easier to understand if you attached a workbook showing a before and after scenario instead of showing as text in the thread. In that manner, we can manipulate the data without having to recreate your workbook.
Amit Rana
New Member
AlanSidman
Well-Known Member
If you change your data to look like the following
Data Range
then use power Query with the following Mcode
then the output looks like the following
Data Range
Data Range
A | B | |
1 | Column1 | Column2 |
2 | 1 | Abc. |
3 | 1 | Bcd |
4 | 1 | Wfg |
5 | 1 | Bsb |
6 | 1 | Hsjh |
7 | 1 | Hshh |
8 | 2 | Anan. |
9 | 2 | Hshjs |
10 | 2 | Bsbh |
11 | 2 | Hshhs |
12 | 3 | Anna. |
13 | 3 | Jsjjjs |
then use power Query with the following Mcode
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Data", each Text.Combine([Column2], ", "), type text}})
in
#"Grouped Rows"Data Range
A | B | |
1 | Column1 | Data |
2 | 1 | Abc., Bcd, Wfg, Bsb, Hsjh, Hshh |
3 | 2 | Anan., Hshjs, Bsbh, Hshhs |
4 | 3 | Anna., Jsjjjs |
Amit Rana
New Member
Amit Rana
New Member
AlanSidman
Well-Known Member
In this section of the code change the comma between the quotes to what ever you wish
Combine([Column2], ", "), type text}
for example if you wish to separate with Pipe then
Combine([Column2], "| "), type text}
Combine([Column2], ", "), type text}
for example if you wish to separate with Pipe then
Combine([Column2], "| "), type text}
Amit Rana
New Member
Thanks a lot you helped me ..but can you guide me with the use of code as I have never used one .in excel ...can you please brief me about its implementation ..please ..
AlanSidman
Well-Known Member
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Amit Rana
New Member
Thanks a lot you helped me ..but can you guide me with the use of code as I have never used one .in excel ...can you please brief me about its implementation ..please ..
Amit Rana
New Member
THANK YOU SIR,, can you let me know what changes i would have to make if there are two more coloumns of mobile no. and id are added ... i want to join then using same range as previous
Last edited:
AlanSidman
Well-Known Member
Post a realistic example as an upload with an explanation of what you want. Show a sample mocked up solution also.
Amit Rana
New Member
SIR, this a mock file i have built .. it has two sheets 1 st one is a question and second is the solution i want .. earlier you gave me a solution it was great for names but now i have more two coloumns to do the same ...
please sir help me .. can you provide me with a code that can do this all at once ...
please sir help me .. can you provide me with a code that can do this all at once ...
AlanSidman
Well-Known Member
1. Bring your Data in Power Query
2. Highlight the first column and then on the Transform Tab, Select Fill and Choose Down.
3. Close and Load your data to a Connection Only and check the box to Add to Data Model
4. Open Power Pivot and click on Pivot Table and Select Pivot Table.
5. Next Follow the instructions in this tutorial to create three Measures in your PT.
https://sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table/
6. Build your Pivot Table as shown in the attached file using your measures.
7. Suggest you adjust the width of the columns and wrap the data
2. Highlight the first column and then on the Transform Tab, Select Fill and Choose Down.
3. Close and Load your data to a Connection Only and check the box to Add to Data Model
4. Open Power Pivot and click on Pivot Table and Select Pivot Table.
5. Next Follow the instructions in this tutorial to create three Measures in your PT.
https://sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table/
6. Build your Pivot Table as shown in the attached file using your measures.
7. Suggest you adjust the width of the columns and wrap the data
AlanSidman
Well-Known Member
In order to pivot the data you need to use the Concantenatex to build the measures to get the data into the same cells. I will look at alternatives in Power Query, but no guarantees at this time. I am thinking that it may have to build lists to concatenate the data but will need to research as this is not something routine for me. Be patient. But know that my prior solution works and gives results as expected.
AlanSidman
Well-Known Member
Ok. Got a solution for you.
You will build three queries and then merge them together as shown in the attached file.
Data Range
You will build three queries and then merge them together as shown in the attached file.
Data Range
A | B | C | D | |
1 | ID | Data | FID.Data | LGD.Data |
2 | 381 | KAMLA DEVI S/O YAAD RAM | 6915743886 | 94666744 |
3 | 370 | KAMLA DEVI S/O YAAD RAM | 6915743886 | 94666744 |
4 | 433 | RAMESH S/O KAILASH | | |
5 | 427 | PURAN S/O DATA RAM, KRISHAN S/O DATA RAM, INDRAJ S/O DATA RAM, PYARE LAL S/O DATA RAM, PARKASH VEER S/O NAR SINGH, KAMLESH S/O NAR SINGH | 3662328442, 5510450891, 2234980127, 2041630319, 7168755154, 5460291901 | 99918774, 99918774, 99918774, 99918774, 99918774, 99918774 |
6 | 449 | KAILASH S/O AMI LAL | 3572964780 | 99918774 |
7 | 469 | ROHTASH S/O HEERA LAL | | |
8 | 477 | VIJAY KUMAR S/O AMI CHAND | 92972108354 | 9466848 |
9 | 360 | KAILASH S/O AMI LAL | 3572964780 | 9466848 |
10 | 372 | KAILASH S/O AMI LAL | 3572964780 | 9466848 |
11 | 334 | BHAGWAN DASS S/O AMI LAL, RAMAVTAR S/O AMI LAL | | 9416726 |
12 | 409 | RAM CHANDER S/O ANGNA RAM, KRISHAN S/O ANGNA RAM, PARTAP S/O RAMSWROOP, RAMKISHOR S/O RAMSWROOP, ASHOK S/O RAMSWROOP | | |
13 | 436 | SUMER SINGH S/O RAM JI LAL | 2440566105 | 981640 |
14 | 560 | PRADEEP S/O MANGE RAM | 4146412390 | 9468009 |
15 | 580 | MAHAVIR S/O MANGLA RAM, PRADEEP S/O MANGE RAM, RAVI S/O KRISHAN KUMAR | 8636037144, 4146412390, 5325352190 | 9811284, 9467009, 7588943 |
16 | 801 | KAMLA S/O HARFOOL, BALBIR S/O BUDHRAM, RAJPAL S/O BHOOP SINGH, OMPARKASH S/O BHOOP SINGH, SUBHRAM S/O BHOOP SINGH, HANSRAJ S/O BHOOP SINGH, JAINARAYAN S/O BUDHRAM, JAGDISH S/O BUDHRAM, ATTAR SINGH S/O BUDHRAM, RAMESH S/O RATI RAM, BALJEET S/O RATI RAM, VINOD S/O LAXMI NARAYAN, VED S/O GANPAT, HOSHIYAR S/O BANWARI LAL, RAMESH S/O BANWARI LAL, RAMNIWASH S/O BANWARI LAL, MAHAVIR S/O BANWARI LAL, RAJESH S/O BASTI RAM, SAJJAN S/O BASTI RAM, PURAN MAL S/O SHIMBHU, SHER SINGH S/O SHISH RAM, RANDHIR SINGH S/O SHISH RAM, SATPAL S/O KUNDAN, DHARMBIR S/O OMPARKASH, VIJAY S/O SULTAN | | 870897, 946667, 94668267 |
AlanSidman
Well-Known Member
Make sure that the column is formatted as text before you change Mcode from List.Sum to Text.Combine
Peter Bartholomew
Well-Known Member
Just to provide options (as it stands, requiring Microsoft 365), I have tried a formula solution (shown on the mock solution sheet).
The key was to create a set of IDs equivalent to the PowerQuery fill down and a list of distinct IDs.
I used the function UNIQUE(ID) to extract a list of distinct IDs (there was a slight mismatch with the mock solution) and then
to return the data
The key was to create a set of IDs equivalent to the PowerQuery fill down and a list of distinct IDs.
Code:
= LET(
k, SEQUENCE(ROWS(Table1)),
k₀, IF(Table1[ID]<>"", k),
XLOOKUP(k, k₀, Table1[ID], , -1) )
Code:
= TEXTJOIN(", ", 1, IF(ID=@DistinctID#, Table1[NAMES], "") )
= TEXTJOIN(", ", 1, IF((ID=@DistinctID#)*( Table1[FID]<>""), Table1[FID], "") )
= TEXTJOIN(", ", 1, IF((ID=@DistinctID#)*( Table1[LGD]<>""), Table1[LGD], "") )Peter Bartholomew
Well-Known Member
These are perfectly standard Excel formulas but written in terms of defined Names and using some recent functions that are only available in Microsoft 365.
If you do not have the SEQUENCE function you could insert a helper field into the table containing the record numbers 'k'. k₀ would be a Named formula. XLOOKUP could be replaced by the older LOOKUP function. UNIQUE IDs can be selected manually or using MATCH/SMALL/INDEX. TEXTJOIN, I think, was an Office 2016 function; it is pretty much indispensable here.
You may well wish to stick with PowerQuery, depending upon the version of Excel you are using.
If you do not have the SEQUENCE function you could insert a helper field into the table containing the record numbers 'k'. k₀ would be a Named formula. XLOOKUP could be replaced by the older LOOKUP function. UNIQUE IDs can be selected manually or using MATCH/SMALL/INDEX. TEXTJOIN, I think, was an Office 2016 function; it is pretty much indispensable here.
You may well wish to stick with PowerQuery, depending upon the version of Excel you are using.
Amit Rana
New Member
Hey peter I am using office 365 .. can you let me know the step by step process what to do ... the earlier solution provided by alan sidman was sucessfull but I should have a alternative too.. because i am not used to work in excel so much i am a beginner to it...
if u could just do the need and tell me what to do ..
i tried to understand the above through internet but i was unabale to..
if u could just do the need and tell me what to do ..
i tried to understand the above through internet but i was unabale to..