Macro or Formula to merge data

[CandiC]

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?

 

[Ken Johnson]

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  Iend
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 wouldonly
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

 

[ogerriz]

This always happens to me however the good thing is that the line 1 of every
data has a distinguishing title so that it actually reads "ITEM# nnnnn", so
what I do is I use that to indicate that is the leading line

If data starts in column A2, you can change the "ITEM" into the common word
or prefix for the leading data line.

In column G I used =IF(left(A2,4)="ITEM",A3,G2) Then in Column H
=IF(left(A2,4)="ITEM",B3,H2)

It is a bit more simplistic but it will work as long as you are dumping the
data from a report.

 

[CandiC]

I have 30,000+ rows of data to merge, would this still work

 

[CandiC]

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?

 

[ogerriz]

Couple of things I forgot to add was that:

it would probably help if you've put a line number in column A in the
original report so that you can always sort it back to how it was from the
original excel dump.

Apply the formulas (either one will work) once you've gotten the items all
in one line, then you should copy paste special value all the formulas before
sorting.

You can then delete the lines where the Item # column (now in col B after
putting line nos. in A) are non-valid nos.



both solutions should work.

 

[Ken Johnson]

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?

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