| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro or Formula to merge data
Downloaded Data format................
Column A.ColumnB..Column C.column D..Column E.Column F ..1item #.........qty expectpurch ord#.....<blank.<blank..<blank ..2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G ..3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd and delete the qty Expect. Please advise if this would need to be written as a macro. Can someone help me please? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro or Formula to merge data
On Apr 22, 7:19*am, CandiC wrote:
Downloaded Data format................ Column A.ColumnB..Column C.column D..Column E.Column F .1item #.........qty expectpurch ord#.....<blank.<blank..<blank .2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G .3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd 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 recds are in the even numbered rows of column B then you could use =INDIRECT("B" & ROW(1:1)*2) to extract the Qty recds 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro or Formula to merge data
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" wrote: Downloaded Data format................ Column A.ColumnB..Column C.column D..Column E.Column F .1item #.........qty expectpurch ord#.....<blank.<blank..<blank .2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G .3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd and delete the qty Expect. Please advise if this would need to be written as a macro. Can someone help me please? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro or Formula to merge data
I have 30,000+ rows of data to merge, would this still work
"ogerriz" wrote: 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" wrote: Downloaded Data format................ Column A.ColumnB..Column C.column D..Column E.Column F .1item #.........qty expectpurch ord#.....<blank.<blank..<blank .2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G .3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd and delete the qty Expect. Please advise if this would need to be written as a macro. Can someone help me please? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro or Formula to merge data
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 .1item #.........qty expectpurch ord#.....<blank.<blank..<blank .2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G .3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd 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 recds are in the even numbered rows of column B then you could use =INDIRECT("B" & ROW(1:1)*2) to extract the Qty recds 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro or Formula to merge data
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. "CandiC" wrote: I have 30,000+ rows of data to merge, would this still work "ogerriz" wrote: 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" wrote: Downloaded Data format................ Column A.ColumnB..Column C.column D..Column E.Column F .1item #.........qty expectpurch ord#.....<blank.<blank..<blank .2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G .3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd and delete the qty Expect. Please advise if this would need to be written as a macro. Can someone help me please? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro or Formula to merge data
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 .1item #.........qty expectpurch ord#.....<blank.<blank..<blank .2..DescriptionQty recd<blankUnit Price..Ext PriceUnit of Meas (Wish list) ..Col A..........Col B....Col C.........Col D...........Col E..Col F..Col G .3..Item#....DescriptionQty Recd.PO#...Unit of Measure..Unit priceExt. 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 Recd 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 recds are in the even numbered rows of column B then you could use =INDIRECT("B" & ROW(1:1)*2) to extract the Qty recds 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 recds, Unit of Meass and Unit Prices are always in even numbered rows Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Permanently merge cells with text data w/o a formula? | Excel Discussion (Misc queries) | |||
| Merge cells with formula or macro? | Excel Discussion (Misc queries) | |||
| mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
| Merge =( formula should retain fraction type numbers after merge. | Excel Worksheet Functions | |||
| how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) | |||
Linear Mode
