Excel Conditional Formatting Rows to Columns

Solved/Closed
em - Apr 24, 2011 at 05:12 PM
 em - Apr 25, 2011 at 03:06 PM
Hello,

I have a data set that looks like:

A B
1 20
2 21
3 21
4 03
1 19
2 34
3 23
4 25
1 23
2 27
3 80
4 01


And I want it to look like:

A B C D
1 20 19 23
2 21 34 27
3 21 23 80
4 03 25 01

If there is an easy way to do this i would love to know. My data set is huge!!

Thanks
Em

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 25, 2011 at 04:26 AM
try this and see sheet2
if you want to use the macro in your original file take a copy and keep it somewhere safely .


Sub test() 
Dim r As Range, rfull As Range, filtrange As Range, cfilt As Range 
Dim x, result As Range 
Worksheets("sheet1").Activate 
Set r = Range(Range("A1"), Range("A1").End(xlDown)) 
Set filtrange = Range("A1").End(xlDown).Offset(5, 0) 
Set rfull = Range("A1").CurrentRegion 
r.AdvancedFilter xlFilterCopy, , filtrange, True 
Set filtrange = Range(filtrange.Offset(1, 0), filtrange.End(xlDown)) 
For Each cfilt In filtrange 
x = cfilt.Value 
rfull.AutoFilter field:=1, Criteria1:=cfilt.Value 
Set result = rfull.Offset(1, 1).Resize(rfull.Rows.Count - 1, rfull.Columns.Count - 1). _ 
    Cells.SpecialCells(xlCellTypeVisible) 
    result.Copy 
With Worksheets("sheet2") 
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = x 
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True 
End With 
rfull.AutoFilter 

Next cfilt 
End Sub 
0
Thank you soooo much. The script works really well. I am working with Julian days so the 366 days get a little mixed up but it is nothing I can't fix. Thanks again for all your help this has saved me hours of copy/paste =)

Cheers,
Emily
0