• 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.

Concatenate names in excel up to a specific range every time

@Amit Rana The starting point is the function that you can see in the refers to box if you open Name Manager and look at the Name 'ID'. You will see that it contains a LET function. The same function is shown in cells F2:H2 where it is developed column by column. LET works by assigning names to fragments of the final formula for reuse, rather than writing them to a cell and referencing the cell. I suggest you look the function up.

Then look at G2 on the Mock solution sheet. The UNIQUE function reduces the IDs to a distinct set. Then for each ID in turn column H reads those [Names] from the table that correspond to the current ID. TEXTJOIN collects the names to form a single comma-separated string. Similarly for the FID and LGD in the following columns.
 

Attachments

  • DataExtraction.xlsx
    148.2 KB · Views: 4
Amit,
Based upon the file you presented, you can see that the solution I provided works. If that file is not representative of your actual file, then please upload one that is more representative.

I cannot guess as to what your issue is without seeing your data.
 
AlanSidman said:
Amit,
Based upon the file you presented, you can see that the solution I provided works. If that file is not representative of your actual file, then please upload one that is more representative.

I cannot guess as to what your issue is without seeing your data.
Click to expand...
Thank you for your concern ..but the thing is I would like to ask a favour ...I would like a small demonstration vedio of the steps u did on file if its possible via whatsapp or telegram or email .
 
AlanSidman said:
Amit,
Based upon the file you presented, you can see that the solution I provided works. If that file is not representative of your actual file, then please upload one that is more representative.

I cannot guess as to what your issue is without seeing your data.
Click to expand...
If you can just present a small vedio of steps .it will be very help full .. contact 9501341081 on telegram
 
Peter Bartholomew said:
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.
Click to expand...
ty
 
AlanSidman said:
Amit, Will not have time to do this for you until sometime on Wednesday, Mountain Time (US).
Click to expand...
Hey Alan can you explain me the method of Peter suggest to me in this post .. Actually I am not aware about name manager and it steps and usage .please ..can u give me step by step methodology in basic level
 
Amit, I cannot help you with Peter's suggestion as he is using a later version of Excel than me. The functionality he is referring to is O365 and I am running XL 2019.
 
Amit
Here is a video I made. I hope you can follow it. In the Power Pivot, to get new measures, you will need to right click. The file is too large to post here so I have posted it to a third party location. I will leave this up for 48 hours. Please download it ASAP as it takes up a bit of my off line storage.
app.box.com

Amit PQ and PowerPivot.zip | Powered by Box

app.box.com app.box.com
Alan
 
You must log in or register to reply here.
Back
Top