Duplicate Entries in Rows - Add Accompanying Data to New Columns

[hamidxa]

I have attached a before and after image of what I am looking to accomplish.

In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)

In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number

What I did however in the after image, as can be seen, is I took the data that appeared on the repeating row (Row 3 from the Before image), and included the PRICE, COST, BEGIN, and END values in new columns on row 2.

The model number is the same of course, which is why I did it that way.

If the model numbers were different, I would have simply left it alone, as-is.


Is there a way to accomplish this via some functions or maybe even a VBA script?

By the way, if we have to dump all of this onto a new sheet, then so be it.


Thanks in advance!

[hamidxa]

If it helps.
The attached file contains a before and after sheet of what Im looking to achieve,

Essentially, for any Model number that appears on more than 1 row,
take the DOC, PRICE, BEGIN, and END values from those rows and append them to the first (instance) row where that Model number appears in new columns.

Consequently, those rows where the model number repeats should be deleted, since the data for those rows should be merged onto the row containing the first instance of that model number.

Hopefully, all of the results can fall onto a new sheet.

[jubiesxl]

Did you try the Pivot Table Option

Select all the cells in Before Sheet >> Insert Tab >> Pivot Table >> On the Row Lables check off Begin (or End if you like that way), in the Column Labels check off End (or Begin in this case if you selected End previously) >> in the Value field you select the Docs

Once the table is set, change the value field settings from sum to Max

HTH

[Alexander Frolov]

Hello hamidxa,
Is it a one-time task or do you need to do this regularly?

[hamidxa]

Did you try the Pivot Table Option

Select all the cells in Before Sheet >> Insert Tab >> Pivot Table >> On the Row Lables check off Begin (or End if you like that way), in the Column Labels check off End (or Begin in this case if you selected End previously) >> in the Value field you select the Docs

Once the table is set, change the value field settings from sum to Max

HTH

I had originally performed this task with a Pivot Table, but the output didn't look "clean" for lack of a better term.

[hamidxa]

Hello hamidxa,
Is it a one-time task or do you need to do this regularly?

It's to be a repeated task.

I found a solution that does the trick for me.

Here is the code that I used for my specific case (by manipulating the Range values; i.e., I set mine to (r,7) and (r-1, 11))

Please Login or Register  to view this content.