Condense duplicate data to one row, data in same column in new row

[resmed]

Hi,
I have a spreadsheet that has "Parts" in the first column and "Due Dates" in the following columns. Currently, the data is spread across rows with the same part appearing more than once with different dates in different columns.

I would like to condense the data to a single row per part but with its dates spread across columns in the same row (and remaining in its same column position as it was originally but in the new row). Preferably with VB.


Example below:

Existing Worksheet (Sheet1)
A1 A2 A3 A4
R1 Part1 10/1
R2 Part1 10/2
R3 Part1 10/3
R4 Part2 10/1
R5 Part2 10/4

Condensed Worksheet (Sheet2)
A1 A2 A3 A4
R1 Part1 10/1 10/2 10/3
R2 Part2 10/1 10/4

I don't know if the spacing in the example will carry into the posted message so I have attached an example spreadsheet (Excel 2007).
Thank you,
Chris


New Parameters:
Condense onto same worksheet (instead of separate worksheet).

[rylo]

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

[resmed]

Thank you! That does exactly what I wanted.

Chris

[resmed]

Thanks, Rylo.

Something has changed - what if I want to perform the function on the same spreadsheet instead of copying the cells to a 2nd spreadsheet?

Chris

[rylo]

Hi

One way would be to create a temporary spreadsheet, use the above macro to put it to that temporary sheet, copy from the temp over the original, then remove the temporary sheet.

Would that suffice?

rylo

[resmed]

Yes, that would be okay.
I would need the copied cells to be put onto an existing sheet (Sheet2) that has labeling across the first 3 rows.
So is there a way to specify that the data be copied starting at row 4?

Thanks again.

Chris

[rylo]

Chris

Put up an updated example file showing your real stucture, how it starts, and how it is to look when completed.

ryl

[resmed]

Hi,
Here is an example of the spreadsheet.

The data is Parts (with some items such as description, cost, etc.) and Due Dates with quantity and weekday.

Sheet "Input" has the data (brought in from an ODBC link to a database) and the various dates (with quantity and weekday) spread out across the columns based on week number.
At this point, the spreadsheet is usable except that a single part appears on more than one row if it has more than one due date. So the only change that would need to occur is the condensing of the mentioned rows to a single row. The dates are already in the correct column, they just need to move to the first row that its related part number appears and the remaining duplicate rows of that part number deleted (after all the dates are moved to the same row).

Sheet "Output" shows how the data should be condensed to a single row per part with the various due dates spread across the columns on the same row (note, the dates did not have to move columns, they just had to move to the first row that the part appeared).

Rows 1-3 contain static labels. Columns 1-7 contain part number, description items such as order, customer part number, etc. These columns/rows will always contain this info. It may not always contain the same part numbers, it changes based on orders/production.

The due dates start at column 8-63. These columns will always contain this info.

Thank you and do let me know if you need any other info or clarification.

Chris

[rylo]

Chris

See how this goes for the example file.

Please Login or Register  to view this content.

rylo

[WorksheetWarrior]

Hey guys, sorry for bumping this old thread but this is very close to what I need. It is as close as I have gotten to finding a solution.

I need something like the first person asked for, except one that can handle a few more columns (preferably adjustable). For instance:

A1 A2 A3
R1 PART1 4 5
R2 PART1 3 7
R3 PART1 6 8
R4 PART1 5 6
R5 PART2 5 3


Would be:


A1 A2 A3 A4 A5 A6 A7 A8 A9
R1 PART1 4 5 3 7 6 8 5 6
R2 PART2 5 3


All of the data I am using may be either numerical or a character.

Any help would be GREATLY appreciated as I am currently using a much more manual time intensive process to achieve this. This is my first post so if I did something wrong don't hesitate to haze me.

Thanks guys.

[yabin1]

Can someone past the fomular here directly without the attachment? it is not allowed to download the file in firm's PC. Thank you in advance.

[arlu1201]

yabin1,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.