how to change data form?

  • Thread starter Thread starter quietning
  • Start date Start date
Q

quietning

I have a form like this:

a 11-010001
b meat
c 1
d 2
a 11-010003
b apple
c 4
d 3
a 11-010012
b orange
c 2
d 4
…………

and i want to change the data above to:
a b c d
11-010001 meat 1 2
11-010003 apple 4 3
11-010012 orange 2 4

don't tell me to use copy & paste because the original data is more
than 60000 rows.

anybody please help!!
 
try this

Sub looper()
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 1).Select
If Cells(a, 1) <> "" Then
Cells(a, 2) = Cells(a + 1, 1)
Cells(a, 3) = Cells(a + 2, 1)
Cells(a, 4) = Cells(a + 3, 1)
Cells(a + 1, 1).Select
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Else
End If
Cells(a + 1, 1).Select
Next
End Sub
 
Hi

Carry this out on a COPY of your data - just in case!!

With your data in column A, enter the following in B1
=INDEX($A:$A,(ROW(B1)-1)*4+COLUMN(B1)-1,1)
and copy across through C1:E1
Copy B1:E1 down as far as required.

Select column B:E>Copy>Paste Special>values
Delete column A
 
Back
Top