On Apr 22, 11:25*pm, CandiC wrote:
I have 30000+ rows to merge, will I need to use these formulas to bring the
data into a new sheet to merge them, or where would I insert the formula?

"Ken Johnson" wrote:
On Apr 22, 7:19 am, CandiC wrote:
Downloaded Data format................
……Column A….ColumnB……..Column C…….column D…..Column E…….Column F

.1…item #.........qty expect……purch ord#.....<blank…….<blank………..<blank
.2..Description…Qty rec’d……<blank…………Unit Price…..Ext Price……Unit of Meas

(Wish list)
…..Col A..........Col B…....Col C….........Col D...........Col E………..Col
F………..Col G

.3..Item#....Description…Qty Rec’d….PO#...Unit of Measure..Unit price…Ext.
recd

This is a receiving report, I am trying to calculate total receipts per item
number, however, when I download the information from our MRP system *I end
up with two rows of information. I need to merge the information in rows 1
and 2 keeping them in line(Wish list) , however, in Column B *, I would only
like to state the total “Quantity Rec’d” and delete the “qty Expect.” Please
advise if this would need to be written as a macro. Can someone help me
please?

If all the Item#s are in the odd numbered rows of column A then you
could use…
=INDIRECT("A"&ROW(1:1)*2-1)
to extract the item#s

If all the Descriptions are in the even numbered rows of column A then
you could use…
=INDIRECT("A" & ROW(1:1)*2)
to extract the Descriptions

If all the Qty rec’ds are in the even numbered rows of column B then
you could use…
=INDIRECT("B" & ROW(1:1)*2)
to extract the Qty rec’ds

If all the purch ord#s are in the odd numbered rows of column C then
you could use…
=INDIRECT("C" & ROW(1:1)*2-1)
to extract the purch ord#s

If all the Unit of Meass are in the even numbered rows of column F
then you could use…
=INDIRECT("F" & ROW(1:1)*2)
to extract the Unit of Meass

If all the Unit Prices are in the even numbered rows of column D then
you could use…
=INDIRECT("D" & ROW(1:1)*2)
to extract the Unit Prices

Ext. recd???

Ken Johnson

Formulas can go anywhere on the same sheet.
If you want to use a new sheet then the original sheet name will have
to be added to each formula.
For example, if the original data is on Sheet1 then the formula for
extracting the Item#s to a different sheet becomes...

=INDIRECT("Sheet1!A"&ROW(1:1)*2-1)

If the original data is on a sheet with a sheet name that includes a
space character then the sheet name needs to be inside single quotes
eg 'My Sheet' and the formula would be...

=INDIRECT("'MySheet1'!A"&ROW(1:1)*2-1)

Then just fill the formulas down as far as needed.
Note that the formulas do require that the layout of the original data
is consistent meaning that Item#s and Purch Ord#s are always in odd
numbered rows while Descriptions, Qty rec’ds, Unit of Meass and Unit
Prices are always in even numbered rows

Ken Johnson