Transpose, skip redundant

[boumboumjack]

Hi everyone,

I am looking for several days now, how to transpose dynamically unique data.

The table I have is that

journal!

a
a
b
c
d
b

And I would like that

Rate!
abcd

BUT I want to be able to add data in the column journal! that would directly goes to the rate! sheet.

Transposing is not a problem but skipping blank cell is impossible for me.

The rate sheet is actualy returning a loooot of data, that's why I want to prevent redundant cells. Order has no importance since I look for the data with =match.

Thank you for your time.

 

[venkat1926]

let A1 have a heading e.g. "hdng" and from A2 down your data
in that case you can do advanced filter and copy the filtered data transposing.


try this macro

Sub test()
Dim r As Range, filt As Range, dest As Range
Worksheets("journal").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
Set filt = Range("A1").End(xlDown).Offset(5, 0)
 r.AdvancedFilter action:=xlFilterCopy, copytorange:=filt, unique:=True
Set filt = Range(filt.Offset(1, 0), filt.End(xlDown))
filt.Copy
Worksheets("rate").Range("A1").PasteSpecial Transpose:=True
Application.CutCopyMode = False

End Sub

be careful about spelling of sheet names. (spelling and no extra spaces)

 

[boumboumjack]

Thank you very much, you made my day! I made a nice button to update the sheet, it roxx thanks to you!

For learning purpose, could you detailed the function?

Have a nice day!

 

[venkat1926]

waht the macro does is
it finds out the unique values in the column using advanced filter
and copy that in another location with transpose

you can use advance filter for getting unique values in a range