Forum Discussion
Solved
Have text list of files, need to group like with like within same cell.
This is for a museum database, we have a list of filenames of photographs. Each filename starts with the collection number. We need to group all the filenames that start with the same prefix into a single cell to be placed into the appropriate record.
We want to turn this:
ANSPIP-000001-photo1
ANSPIP-000001-photo2
ANSPIP-000001-photo3
ANSPIP-000002-photo1
ANSPIP-000002-photo2
ANSPIP-000003-photo1
ANSPIP-000003-photo2
ANSPIP-000003-photo3
ANSPIP-000003-photo4
into
ANSPIP-000001-photo1; ANSPIP-000001-photo2; ANSPIP-000001-photo3
ANSPIP-000002-photo1; ANSPIP-000002-photo2
ANSPIP-000003-photo1; ANSPIP-000003-photo2; ANSPIP-000003-photo3; ANSPIP-000003-photo4
I am attaching a set of sample data to test with, a formula or macro (more likely) is fine! Thank you!
As variant
=LET(names, $A$1:$A$29, GROUPBY(TEXTBEFORE(names, "-"),names,ARRAYTOTEXT,3,0) )
4 Replies
As variant
=LET(names, $A$1:$A$29, GROUPBY(TEXTBEFORE(names, "-"),names,ARRAYTOTEXT,3,0) )That definately looks like it should suit our purposes, thank you!
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.
This needs to be as automated as possible. There's easily 100k records, with multiple photos for each. Thank you, though!
