Solved: Splitting data from one column into multiple columns in excel

[Beatrix]

Dear All ,

I have been searching for a VBA code on the internet to split some values from 1 column into the 4 different columns based on a criteria in Excel 2007..

Each record has 4 test codes however they are placed into 4 rows instead of 4 columns

For example:

Column H has test codes as NFE/NFM/NFR/QTV (they are not in a order)

Column I has test scores

record1 nfe 90
nfm 70
nfr 40
qtv 60
record2 nfr 50
qtv 75
nfm 55
nfe 80

I want to create one column for each test code and split the test scores from column I to others by using test code criteria in column H.

There are more than 600 records..I set some if functions to assign them to related column however they go to different row numbers, I need to split 4 test codes into 4 columns in 1 row for one record.

I don't know too much about VBA but I'm learning..

Your help would be much appreciated ..

Regards,
Yeliz

[stanleydgrom]

Yeliz T,

Please post your workbook with before and after worksheets.

Click on the Post Reply button, and scroll down and see Manage Attachments.

[Beatrix]

Yeliz T,

Please post your workbook with before and after worksheets.

Click on the Post Reply button, and scroll down and see Manage Attachments.

[Kenneth Hobs]

Sub Split4ColTo1Row()
Dim startcell As Range, col As Range, cell As Range, i As Integer
Set startcell = Range("B3")
Do While startcell.Value2 <> ""
For i = 0 To 3
If startcell.Offset(i, 0).Value2 = Range("D2").Value2 Then startcell.Offset(0, 2).Value2 = startcell.Offset(i, 1).Value2
If startcell.Offset(i, 0).Value2 = Range("E2").Value2 Then startcell.Offset(0, 3).Value2 = startcell.Offset(i, 1).Value2
If startcell.Offset(i, 0).Value2 = Range("F2").Value2 Then startcell.Offset(0, 4).Value2 = startcell.Offset(i, 1).Value2
If startcell.Offset(i, 0).Value2 = Range("G2").Value2 Then startcell.Offset(0, 5).Value2 = startcell.Offset(i, 1).Value2
Next i
Set startcell = startcell.Offset(4, 0)
Loop
End Sub

[GTO]

I am 'formula challenged', but this seems to work.

For the first row:

D3) =INDEX($B3:$C6,MATCH($D$2,$B3:$B6,0),2)
E3) =INDEX($B3:$C6,MATCH($E$2,$B3:$B6,0),2)
F3) =INDEX($B3:$C6,MATCH($F$2,$B3:$B6,0),2)
G3) =INDEX($B3:$C6,MATCH($G$2,$B3:$B6,0),2)

...copied down...

Mark

[mbarron]

If you want to use a formula, you can use:
=IF($B3=D$2,$C3,"")
Enter the formula in D3 then drag right then copy down (with B3:E3 is selected -double click on the fill handle to copy down quickly).

If you want a macro:
Sub breakout()
Dim lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
With Range("D3:G" & lRow)
.Formula = "=IF($B3=D$2,$C3,"""")"
.Value = .Value
End With
End Sub

[Beatrix]

Sub Split4ColTo1Row()
Dim startcell As Range, col As Range, cell As Range, i As Integer
Set startcell = Range("B3")
Do While startcell.Value2 <> ""
For i = 0 To 3
If startcell.Offset(i, 0).Value2 = Range("D2").Value2 Then startcell.Offset(0, 2).Value2 = startcell.Offset(i, 1).Value2
If startcell.Offset(i, 0).Value2 = Range("E2").Value2 Then startcell.Offset(0, 3).Value2 = startcell.Offset(i, 1).Value2
If startcell.Offset(i, 0).Value2 = Range("F2").Value2 Then startcell.Offset(0, 4).Value2 = startcell.Offset(i, 1).Value2
If startcell.Offset(i, 0).Value2 = Range("G2").Value2 Then startcell.Offset(0, 5).Value2 = startcell.Offset(i, 1).Value2
Next i
Set startcell = startcell.Offset(4, 0)
Loop
End Sub

Thank you so much Kenneth !!....

It's working perfectly..Brilliant...I'll study more VBA Excel to be able to write something like this..

Regards,
Yeliz

[Beatrix]

If you want to use a formula, you can use:
=IF($B3=D$2,$C3,"")
Enter the formula in D3 then drag right then copy down (with B3:E3 is selected -double click on the fill handle to copy down quickly).

If you want a macro:
Sub breakout()
Dim lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
With Range("D3:G" & lRow)
.Formula = "=IF($B3=D$2,$C3,"""")"
.Value = .Value
End With
End Sub



Hi ,

Thak you so much for your time, I tested formula an the code, they both split the scores into 4 columns but in different rows rether than same row..

Regards,
Yeliz

[Beatrix]

I am 'formula challenged', but this seems to work.

For the first row:

D3) =INDEX($B3:$C6,MATCH($D$2,$B3:$B6,0),2)
E3) =INDEX($B3:$C6,MATCH($E$2,$B3:$B6,0),2)
F3) =INDEX($B3:$C6,MATCH($F$2,$B3:$B6,0),2)
G3) =INDEX($B3:$C6,MATCH($G$2,$B3:$B6,0),2)

...copied down...

Mark

Cheers Mark ,

Thank you for your reply, I tested the formula, this worked as well but this way, I need to clear every 3 rows between the records..Anyway It's good to know that I can use Index and match function together in this case..

Regards,
Yeliz

[GTO]

Cheers Mark ,

Thank you for your reply, I tested the formula, this worked as well but this way, I need to clear every 3 rows between the records..Anyway It's good to know that I can use Index and match function together in this case..

Regards,
Yeliz

Sorry, I should have been clearer. I meant copy the first row once set up, then just paste every four rows.

(Shucks, I'm happy I seem to have actually gotten a formula right!:yes )