Conversion of Forms into spreadsheet row wise

[Frank Situmorang]

Hello:

We have Purchase orders type in excel all the way down with the same height
( number of rows is the same), now I want to convert so many purchase orders
to be row wise in order to maniputalte data liek sorting by material
description and then we know the lowest price historically.
This is my form of P.O ( more or less). in excell

To : Supplier P.O No.
P.O Date

PO Line Description Qty U.Price Ext. Price
--------- ------------ ----- -------- ---------
1. abc 2 100 200
2. xyz 4 200 800
----------
Total 1,000.-
If there is no sppec below the description, I can do it, but it could be the
other p.o is like this:
PO Line Description Qty U.Price Ext. Price
--------- ------------ ----- -------- ---------
1. abc 2 200 400
Spec1
spec2
spec3
------------
Total 400
How can I make it the specification was typed downword and could be many
lines and some times is coutinued with the other item with only 1 spec in the
description column

It is too tough for me, please help, I appreciate your idea.

Frank

 

[Joel]

Frank: It is not clear exactly what you are looking for.

1) Are are the PO on one worksheet?
2) How do we tell where the last PO is located?
3) What do you want to do with the total Row?
4) what do you want to do witth the Spec data. do you wand to combine with
abc so it reads in one cell abc Spec1, Spec2, Spec3. or do you want to put a
1 in column A?

 

[Frank Situmorang]

Joel:
1.
Yes, P.O is a form but typed in excel, all p.os are in a worksheet, the
clreck just copy id down and then, if she wanted to type a new P.O, just
change, like the supplier name, etc,etc,
2. We do not need to tell it, because whant I did to make it row wise
jsut put formula beside the P.O, for example:
PO Line Description Qty U.Price Ext. Price
--------- ------------ ----- -------- --------- +(from
cell abc). ......
1. abc 2 100 200
2. xyz 4 200 800

3. what I mean is that if we have made a complete formula for 1 form, we can
easily copy it down, to have the same result, after that we can copy range
value the formula in order to change it to text only and we can data sort it
later and delete all the p.o forms
4. I want it to be stated in the row of the material description, as I said
I want to compare apple to apple in order to choose the lowest price, if we
want to bargain it with suppliers.

And this is my problem that the position of spec data is variable meaning
not the same for all po's , I stucked on this now, when making a formula, I
can not uniformly take it from the certain cells

Hopefully you can help me.

Frank

 

[Joel]

Note sure if this will work because I don't know if there is a number in
column C. I assumed all the PO are in columns A thru E. I also didn't know
if the PO numbers in column A needed to be changed, sio I left them the same
as they were. Read the comments in the code.




Sub Get_PO()

'Use column C to determine Last row
LastRow = Cells(Rows.Count, "C").End(xlUp).Row

NewRow = 1
For RowCount = 1 To LastRow

'Look for a number in column c to determin which rows arre POs
If WorksheetFunction.IsNumber(Cells(RowCount, "C")) Then


'copy PO to column H
Range("A" & CStr(RowCount) & ":E" & CStr(RowCount)).Copy _
Destination:=Range("H" & CStr(NewRow))
NewRow = NewRow + 1

End If

Next RowCount

End Sub