View Full Version : Solved: Splitting data from one column into multiple columns in excel
Beatrix
07-01-2010, 04:43 PM
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
07-01-2010, 05:26 PM
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
07-01-2010, 05:38 PM
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
07-01-2010, 07:02 PM
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
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
07-02-2010, 03:44 AM
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
07-02-2010, 07:00 AM
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
07-02-2010, 07:03 AM
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
07-02-2010, 07:11 AM
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
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 )
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.