Transfer cell values to another sheet, but need to skip duplicates

[VBA FTW]

Hi all,

I need to create a macro that will transfer cell values to a new sheet, but I need it to skip duplicate values and move on to the next cell, and end sub on the first blank value. All of my duplicates will be grouped together. Column B will look something like:

1100079878
1100079878
1100895461
1112548741
1112548741

So on my new sheet I just need 1100079787, 1100895461, and 1112548741 because the others are duplicates. I would think this would be some kind of an If Then statement combined with a loop, but I'm not sure where to start. Any advice for + rep?

[patel45]

there are many modes to do, try this

Please Login or Register  to view this content.

[VBA FTW]

"Variable not defined" and it highlights LR

[patel45]

I'm sorry, the right line is
LR = Cells(Rows.Count, "B").End(xlUp).Row
but look for possible type errors

[HaHoBe]

Hi, VBA FTW,

if youīre using Option Explicit itīs mandatory to define each variable used.

Please Login or Register  to view this content.

Ciao,
Holger

[VBA FTW]

Hey thanks for your help, and sorry for the delayed response, I think we're almost there. Here's the code:

Please Login or Register  to view this content.

And here's what I'm getting:
12345 12345
12345 12345
125565 125565
189878 189878
787478 787478
58698 58698
58698
12345

The first column are my actual numbers from my original sheet. The 2nd column is my results. The macro left out the last 2 duplicates, but not the first one. Any thoughts? After I get this straightened out I'm going to need to figure out how to transpose these numbers onto the same row, but first things first

---------- Post added at 02:25 PM ---------- Previous post was at 02:18 PM ----------

I just changed my range from B9 to B8, and it gets rid of the first duplicate (above post), but it is taking what's in B8 and putting on my sheet. B8 says "Invoice Number" so I need a work around for this

[VBA FTW]

Actually, I can just re-organize sheet2 so having my range as B8 will be fine. However, is there any way to transpose this data and get it to A16:H16 instead of J17:J22?

[HaHoBe]

Hi, VBA FTW,

regarding your information about the duplicates: youīre sure thereīs a heading above the raneg you check for duplicates? If not, the first item in thst range is considered to be the heading - no duplicate for the heading.

Regarding transposing: I pretty much doubt that functionality (Iīm not that much famliar with the Advanced Fiter to tell if itīs possible or not). Best way might be to read the values into an array and use that for output (and again: I donīt know if itīs possible).

Afraid to not really have offered any real help for this topic.

Ciao,
Holger

[VBA FTW]

Holger, thanks for the response. I'll have to read up on arrays as I don't have any experience using it. Your input is appreciated, the more I hear, the more I learn. I'm going to do some research and see if I can work something out.

[VBA FTW]

Well, I've figured it out. Not the slickest code, but it accomplishes what I need it to so I'm satisfied. Thanks for the input guys. I've attached a file that has the code if anyone wants to look at it. Thanks again