Moving data from single to multiple columns and merging

Hello,

I know I am going to spend hours if not days trying to figure this out (as I have in the past), and probably end up with nothing. This forum has been extremely helpful before. Here's a problem I've currently run into, and hope someone can kindly assist..

Current Data:
File 1:
Each set of data is listed in either two or three rows

Eg.,
ID
Date
Filename

ID
Date

ID
Date
Filename

The goal is to move them to separate columns (rows can be 3 or 2 for each data set, and may or may not be separated by space/additional row)


File 2:
Has a common field 'ID' as that of File1, does not have Date, and Filename, but has a new field 'Detail' (already in the expected format)

Eg.

ID Detail

The goal is to merge properly formatted data from File 1 to File 2

Eg

ID Date Filename Detail

I'll be happy to clarify further if needed. Sample data attached.

Many thanks in advance.

bump...
Waiting on someone's input.

Thank you.

Thanks for looking.

"Before Merging" is File2.
The end goal is to Merge File1 and File2.

Correct, it's a separate file/workbook.

Thanks.

See if the attached is what you are after.

Jindon,

Thank you! That should do it. However I need to get that Macro applied to my two files File1 and File2. How would this solution fit into that puzzle? In other words, how can I port that to my File1, fix transpose issue, then merge with File2?

Apologies if it's too basic!

TY.

What do you mean by "File1" and "File2"?

Jindon,

As per my original note, there are two files - and the details are as follows:


File 1:

Eg.,

ID
Date
Filename

ID
Date

ID
Date
Filename

The goal is to move them to separate columns (rows can be 3 or 2 for each data set, and may or may not be separated by space/additional row)


File 2:
Has a common field 'ID' as that of File1, does not have Date, and Filename, but has a new field 'Detail' (already in the expected format)


Eg.

ID Detail

The goal is to merge properly formatted data from File 1 to File 2

Eg

ID Date Filename Detail


Thanks.

Can you upload workbooks of File1 and File2 with the final result that you want?

Apologies for the delay. Here are the separate files (really the same data, broken down into separate workbooks):

File1: Data in the original format on a single column (First Tab)
Data transposed into multiple columns (Second Tab)

File2: Separate data, already in proper format

Final: File1 and File2 merged upon File1 reformatting (based on 'ID' field)
(Prefer sorting by Date; but that's something I can probably do myself)

Really the goal is to transpose File1 entries and merge the output with File2.

Thank you again for the help!

Try
Assuming every workbooks are open

Please Login or Register  to view this content.

Thanks.

Attached is what I received when I tried running the code:

Open "Final.xlsm" and click on the button.

Works exactly the way I requested, but unfortunately the requirement changed a bit; for which we had to leverage MS Access to get it completed.

Thank you for all the help! This is good to close.