Excel Conditional Formatting Rows to Columns [Solved/Closed]

Report
-
 em -
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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 
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