Excel - Merge multiple cells into one with some weird critera

[mark17022]

Hello,

I am stumped on how to attempt this. I have read through some posts but here but I'm quite a novice at Excel so not sure what way to go.

I am trying to add metadata to sound effects files. They came with a PDF which I've converted in to an xlsx. Normally you get one line for each sound file and it's as easy as copying the

description column and pasting it into the metadata writing program. Problem I have with this CD is that multiple sounds are recorded in one file, so the description is in multiple rows.

I need to combine all the descriptions for each file into one cell, then remove duplicate words, so I can then paste it into each single files description.

B column shows it's the same file by it's leading number.

File 1 = 1-1, 1-2
File 2 = 2-1, 2-2, 2-3, 2-4
File 3 = 3-1, 3-2, 3-3, 3-4, 3-5
and so on for 990 files

So for File 1 my output would be C2+D2+C3+D3 =
AIR, JET STRONG STREAM THROUGH ANIMAL, FOOTSTEP ANIMAL FOOTSTEPS IN DIRT: VARIOUS MOVEMENTS


Does this seem like it's doable? I tried banging my head on the wall but it didn't help


You can see from the first few entries that certain files won't matter like File 3 but I really need to have the others have searchable metadata.

CD #(A) Tr / In(B) Description1 (C) Description2 (D)
FS01 1-1 AIR, JET STRONG AIR STREAM THROUGH JET
FS01 1-2 ANIMAL, FOOTSTEP ANIMAL FOOTSTEPS IN DIRT: VARIOUS MOVEMENTS
FS01 2-1 APPLAUSE, CROWD SMALL INDOOR CROWD APPLAUSE WITH SLIGHT BUILD
FS01 2-2 APPLAUSE, CROWD SMALL INDOOR CROWD APPLAUSE
FS01 2-3 APPLAUSE, CROWD SMALL INDOOR CROWD APPLAUSE
FS01 2-4 APPLAUSE, CROWD SMALL OUTDOOR CROWD APPLAUSE WITH VOICES
FS01 3-1 APPLAUSE, CROWD SMALL CROWD APPLAUSE
FS01 3-2 APPLAUSE, CROWD SMALL CROWD APPLAUSE
FS01 3-3 APPLAUSE, CROWD SMALL CROWD APPLAUSE
FS01 3-4 APPLAUSE, CROWD SMALL CROWD APPLAUSE
FS01 3-5 APPLAUSE, CROWD SMALL CROWD APPLAUSE

[:) Sixthsense :)]

Pictures are nice to see but we can't work with it.

So please attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[dredwolf]

Please Upload a sample WORKBOOK, pictures are nice, but they show nothing of what we need to give you an answer (ie - data structure, formatting, where the relevant data comes from, etc....)

[mark17022]

Ok sorry about that. I grabbed some sample data from Sheet 1 and pasted it into Sheet 2 and then showed how I would like the result of each group to be. Hope it makes sense. Thanks for you help.

[mark17022]

I just realised it may be easier if the 2 description columns were in one so I re-converted the PDF. Not sure if that helps?

[dredwolf]

See if attached is close to what you want (I used your first sample; as it was easier to weed out duplication )

Hope this helps

[mark17022]

Dredwolf you are the coolest! I love that it just comes to you guys so easily. I was close to something like what you have but it wasn't as elegant and error proof haha. Thanks so much dude. I learnt lots going over the way you attacked it too!

[dredwolf]

You are welcome
It's not so much easy, we just see lots ** " similar " questions really;
So we get get to know what people expect I guess
AND I see the "CENSOR BOT" is working on your messages as well
Anyways, glad taht what i suggested work, and thanks for the kudos

[dredwolf]

Someone pleazzze kill the censor bot!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[dredwolf]

BTW..Last message was all caps, and cb downgraded it...wtfh is going on here???