Using VBA to Spit out Unique IDS from one sheet into another sheet???

[mbarnett]

I have no clue on how to do this. I don't want to use Data Validation tool in Excel. I basically want to run a macro to read thru a range of cells and paste in another sheet the Unique IDs??? I found a function on line that counts unique ids, but I can't figure out how to use the code in a sub.

Any help would be greatly appreciated


Function CountUnique(ByVal MyRange As Range) As Integer
Dim Cell As Range
Dim J As Integer
Dim iNumCells As Integer
Dim iUVals As Integer
Dim sUCells() As String

iNumCells = MyRange.Count
ReDim sUCells(iNumCells) As String

iUVals = 0
For Each Cell In MyRange
If Cell.Text > "" Then
For J = 1 To iUVals
If sUCells(J) = Cell.Text Then
Exit For
End If
Next J
If J > iUVals Then
iUVals = iUVals + 1
sUCells(iUVals) = Cell.Text
End If
End If
Next Cell
CountUnique = iUVals
End Function

 

[SkipVought]

Hi,

You're going to alot of unnecessary effort.

You can get a unique list using either Advanced Filter, Pivot Table or MS Query via Data/Get External Data...

Probably the Pivot Table can give you exactly what you need. You really have not described WHAT you need. Except you stated what tool you did not want to use (don't believe i'd choose Data/Validation either) and some macro that does something???

Rather, it moght have been more productive to have clearly and concisely defined your requirement.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

seconded - easiest and best bet is ADVANCED filter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mbarnett]

Hi Guys,

Thanks for the reply. I want only unique values. Not sure how to do it in VBA. There might be 75 of the same ids. I only want one of the 75. Is that clear

 

[SkipVought]

ADVANCED filter!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[mbarnett]

Sorry - I do not want to use Advanced Filter. When I use it it does not work properly. It double counts the first cell value. I was just wondering if there was code out there that would find unique cells

 

[SkipVought]

It would be foolish to refuse to use this tool! Learn to use it instead!

Advanced Filter DOES work properly. Your USE of it may be lacking, I surmize.

Use NO CRITERIA, Copy to a new location.



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[KenWright]

mbarnett:-
==============
If Advanced Filter appears to double count your first cell then either you haven't set up the ranges right, or the two pieces of data are not the same, eg:-

abcd
abcd

These two are NOT the same, as the second has a trailing space, and as such Adv Filter would return both of them to me in a list, but on the surface they would appear to be the same. Appearances can be deceiving!!

Advanced filter will do exactly what you want to do, and can be incorporated into code if you want also.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

Hi mbarnett,

In your second sheet, do you want to list:
a) only those items that appear exactly once; or
b) one record for each item that appears at least once,
in the first sheet?

The answer differs according to your requirements.

Cheers

 

[mbarnett]

Hi macroprod -

Thanks for your reply. I want one record for each item that appears at least once in the first sheet.

MThanks Again

 

[SkipVought]

ADVANCED filter!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[mbarnett]

No Disrespect SkipVought. As I mentioned in my previous posts advanced filter is not working properly and is double counting the first value. So ADVANCED FILTER IS NOT A OPTION!!! If you can suggest or help with code I would apprciate it, otherwise please no more mention of Advanced Filter. i tried that option and it did not work for me

 

[PHV]

Have you tried the Advanced Filter way with an header row ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KenWright]

mbarnett - If you want to send me the set of data that you belive AF cannot handle, I will happily take a look at it for you. Skip is telling you that 2+2=4 and you are telling him it equals 5. He knows it works. I know it works. We use this stuff day in day out, time and time again, and every time it works - I've yet to come across an instance where it doesn't, and that any misunderstanding hasn't just been an issue with the data itself.

My addy is ken dot wright at ntlworld dot com

Regards
ken............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[pbrodsky]

What do you plan to do with the list after you create it? That might make a difference. Also, have you checked that the duplicate values from Advanced Filter really were the same, or could they be different but looked the same as Ken suggested?

 

[KenWright]

Actually I'm betting that PHV nailed it - Op doesn't have a Header on his data. That will return a duplicated first record if first and second happen to be the same, because Excel treats the first value as a header and quite rightly leaves it there.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Excel HELP on list

Guidelines for creating a list on a worksheet
Microsoft Excel has a number of features that make it easy to manage and analyze data in a list. To take advantage of these features, enter data in a list according to the following guidelines.

List organization
Use only one list per worksheet Avoid having more than one list on a worksheet. Some list management features, such as filtering, can be used on only one list at a time.

Put similar items in one column Design the list so that all rows have similar items in the same column.

Keep the list separate Leave at least one blank column and one blank row between the list and other data on the worksheet. Excel can then more easily detect and select the list when you sort, filter, or insert automatic subtotals.

Position critical data above or below the list Avoid placing critical data to the left or right of the list; the data might be hidden when you filter the list.

Show rows and columns Make sure any hidden rows or columns are displayed before making changes to the list. When rows and columns in a list are not showing, data can be deleted inadvertently.

List format
Use formatted column labels Create column labels in the first row of the list. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the list. Format the cells as text before you type the column labels.

Use cell borders When you want to separate labels from data, use cell borders — not blank rows or dashed lines — to insert lines below the labels. How to apply borders to cells.

Avoid blank rows and columns Avoid putting blank rows and columns in the list so that Excel can more easily detect and select the list.

Don't type leading or trailing spaces Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell.

Extend list formats and formulas When you add new rows of data to the end of a list, Excel uses consistent formatting and formulas. Three of the five preceding cells must use the same format or formula for this to occur. How to turn on extended list formats and functions.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

What are LIST & TABLE PRINCIPLES for Spreadsheet Users faq68-5184

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}