Copying duplicate rows down

[edtwiest]

Hello everyone,

i hope someone can help me out; i put the question in the excelsheet, but here a summary;

With vlookup i gathered some results from other worksheets.
There is a column called "Firm" with a standard value "3000"

Now i need to do the following; for every row i found results (the formula in the upper row will be copied down every time) i need a duplicate of that record with only one modification; the value "3000" has to be replaced in the duplicates with 3005, 3010 and 3010

Please please help me!!! Thank you all in advance!!

Since i can't post an excelsheet, i hope the wetransferfile can be downloaded.....

https://www.wetransfer.com/downloads/5cf8df873c1aee369a16e964485252da20151110214638/0ce2a89f9e8fc05243f9f827d5a1108e20151110214638/d14e11

 

[alexw123]

I can't figure out how to provide a link to the spreadsheet but i think i might have a solution.

Firstly you need to insert a load of new rows, select all the article numbers copy and paste them 3 times below each other, then do your look ups to get the rest of the info for each row. So essentially you have 4 duplicate article numbers

eg. [TABLE="width: 163"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1000187[/TD]
[/TR]
[TR]
[TD="align: right"]1020508[/TD]
[/TR]
[TR]
[TD="align: right"]5608084[/TD]
[/TR]
[TR]
[TD="align: right"]7940540
[TABLE="width: 163"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1000187[/TD]
[/TR]
[TR]
[TD="align: right"]1020508[/TD]
[/TR]
[TR]
[TD="align: right"]5608084[/TD]
[/TR]
[TR]
[TD="align: right"]7940540[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 163"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1000187[/TD]
[/TR]
[TR]
[TD="align: right"]1020508[/TD]
[/TR]
[TR]
[TD="align: right"]5608084[/TD]
[/TR]
[TR]
[TD="align: right"]7940540
[TABLE="width: 163"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1000187[/TD]
[/TR]
[TR]
[TD="align: right"]1020508[/TD]
[/TR]
[TR]
[TD="align: right"]5608084[/TD]
[/TR]
[TR]
[TD="align: right"]7940540[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Create a new column in column I (put this formula in I25) =COUNTIF($C$25:C25,$C$25:$C$40)
Create another column in column J Your new firm column (put this formula in J25) =IF(AND(I25=1),3000,IF(I25=2,3005,IF(I25=3,3010,IF(I25=4,3015,))))

then drag formulas down. Does this give you what you want?

I've basically given each duplicate value in column c a number in sequential order .

eg. if column c was colours

Red 1
Blue 1
Green 1
Green 2
Black 1
Red 2
Blue 2
Blue 3
Black 2
Red 3

Then the if formula in J gives a value of 3000 to any number 1's in Column I, a value of 3005 to any number 2's ect

Ps. How did you get the link to work? to do pay for web link?

 

[alexw123]

correction to the if formula COUNTIF($C$25:C25,$C$25:$C$40) the highlighted range should be however large your C column ends up once you've pasted all the article number 3 times

 

[edtwiest]

This is not exactly what i ment; i wanted to have it done automatically (not with a lot of handwork)

I just went to Transfer (for free) and all i had to do is select the items to upload, the user i want to send (in this case my own email-address) it to and my own email-address....

Can you please have a second look? Or maybe someone else has a suggestion?