Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating a certain number of entries based on a number in a cell
My request sounds simple, but I have yet been unable to do it.
I have a worksheet with a column that has a shipment id, say "H-5" I also have another column that lists the number of samples in that shipment, say 3. I need something that will create two new columns, one with the shipment id and the other with the sample number. So based on the above entry it would create three rows: A B 1 H-5 1 2 H-5 2 3 H-5 3 Since there were three samples, it created three entries for each sample and then would continue on for each entry on the original worksheet, say H-6, five samples. Any suggestions would be greatly appreciated. Thanks, -Derek |
#2
|
|||
|
|||
In A1 use =IF(ROW()<=number_of_samples,shipment_Id,"")
In B1 =IF(ROW()<=number_of_samples,shipment_Id,"") and just copy down to your maximum number of samples of rows -- HTH Bob Phillips "PPV" wrote in message ... My request sounds simple, but I have yet been unable to do it. I have a worksheet with a column that has a shipment id, say "H-5" I also have another column that lists the number of samples in that shipment, say 3. I need something that will create two new columns, one with the shipment id and the other with the sample number. So based on the above entry it would create three rows: A B 1 H-5 1 2 H-5 2 3 H-5 3 Since there were three samples, it created three entries for each sample and then would continue on for each entry on the original worksheet, say H-6, five samples. Any suggestions would be greatly appreciated. Thanks, -Derek |
#3
|
|||
|
|||
Say your shipment id data is on column A and number of samples on column B
and you wanted the desired output beginning C1 on cell C1 simply type your first shipment id and on D1 type 1 Then on C2 type the following: =IF(COUNTIF($C$1:C1,C1)INDEX($B$1:$B$700,MATCH(C1 ,$A$1:$A$700,0))-1,INDEX($A$1:$A$700,MATCH(C1,$A$1:$A$700,0)+1),C1) And on D2 type the following =IF(C2=C1,D1+1,1) Now select C2 and D2 and fill all the way down till you see #N/A "PPV" wrote in message ... My request sounds simple, but I have yet been unable to do it. I have a worksheet with a column that has a shipment id, say "H-5" I also have another column that lists the number of samples in that shipment, say 3. I need something that will create two new columns, one with the shipment id and the other with the sample number. So based on the above entry it would create three rows: A B 1 H-5 1 2 H-5 2 3 H-5 3 Since there were three samples, it created three entries for each sample and then would continue on for each entry on the original worksheet, say H-6, five samples. Any suggestions would be greatly appreciated. Thanks, -Derek |
#4
|
|||
|
|||
With your shipment IDs in col. A and total samples shipped in col. B on
Sheet1, in a new sheet: 1. Copy the name of the first shipment ID into A1. 2. In A2 enter this an fill down until you see a 0: =IF(COUNTIF($A$1:A1,A1)<VLOOKUP(A1,Sheet1!A:B,2,0) ,A1,INDEX(Sheet1!A:A,MATCH(A1,Sheet1!A:A,0)+1)) 3. In B1 insert this and fill down: =COUNTIF($A$1:A1,A1) HTH Jason Atlanta, GA "PPV" wrote: My request sounds simple, but I have yet been unable to do it. I have a worksheet with a column that has a shipment id, say "H-5" I also have another column that lists the number of samples in that shipment, say 3. I need something that will create two new columns, one with the shipment id and the other with the sample number. So based on the above entry it would create three rows: A B 1 H-5 1 2 H-5 2 3 H-5 3 Since there were three samples, it created three entries for each sample and then would continue on for each entry on the original worksheet, say H-6, five samples. Any suggestions would be greatly appreciated. Thanks, -Derek |
#5
|
|||
|
|||
With the shipment IDs in col. A and samples in col B, all on Sheet1:
1. On a new sheet copy in the first shipment ID into A1: 2. In A2 enter this and fill down: =IF(COUNTIF($A$1:A1,A1)<VLOOKUP(A1,Sheet1!A:B,2,0) ,A1,INDEX(Sheet1!A:A,MATCH(A1,Sheet1!A:A,0)+1)) 3. In B1 enter this and fill down: =COUNTIF($A$1:A1,A1) HTH Jason Atlanta, GA "PPV" wrote: My request sounds simple, but I have yet been unable to do it. I have a worksheet with a column that has a shipment id, say "H-5" I also have another column that lists the number of samples in that shipment, say 3. I need something that will create two new columns, one with the shipment id and the other with the sample number. So based on the above entry it would create three rows: A B 1 H-5 1 2 H-5 2 3 H-5 3 Since there were three samples, it created three entries for each sample and then would continue on for each entry on the original worksheet, say H-6, five samples. Any suggestions would be greatly appreciated. Thanks, -Derek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
Picture to appear based on Product Number | Excel Discussion (Misc queries) | |||
returning a text cell based on a number cell | Excel Worksheet Functions |