Forum Discussion

nargis20's avatar
nargis20
Copper Contributor
Feb 08, 2022

Combining data from columns to a single cell

Hi, I want to copy data from multiple columns where common denominator is the company and add all the IDs in a single row.

I tried Concat function but it is good for small data. I have 7,000+ entries. Pivot table is also not helpful. For a better understanding, I have attached a sample. 

Appreciate if anyone can help. let me know if further clarification required.

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Feb 08, 2022
    Assuming company names are in A2:A7000, ID's are in B2:B7000, and you have office 365, I think you could try:

    D2 = Unique(Company)
    E2 = Textjoin(";", True, Filter(B$2:B$7000, A$2:A$7000=D2))

    But, I believe textjoin has a limit of 32767 characters, so depending on much you have for each company it might not work.

Resources