find every different text or data in a column - for Excel
 

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

Thanks for your quick response. Perhaps I posted this in the wrong section
because this is obviously "C" Language. I learned BASIC and assembler, but
not "C". I can teach myself to do this but I was hoping that there was a way
to do this with basic functions in Excel. Is there a cut and paste way to
insert this code into Excel?

Thanks Again,
Tim


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

Actually, what I posted was just the VBA way to do an Excel Advanced Filter...

Using my previous example:
If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
A2:A20 (your list of values)
C1: Field1

Select A1:A20
DataFilterAdvanced Filter
Check: Copy to another location
Copy To: C1
Check: Unique Values
Click [OK]

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Is that more what you were looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

Thanks for your quick response. Perhaps I posted this in the wrong section
because this is obviously "C" Language. I learned BASIC and assembler, but
not "C". I can teach myself to do this but I was hoping that there was a way
to do this with basic functions in Excel. Is there a cut and paste way to
insert this code into Excel?

Thanks Again,
Tim


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

This is what I get for asking complex questions late at night. ;)
I'll try to be less vague and more to the point.
I'm creating a general ledger for my personal finances. I would like to have
another sheet programmed with a function/formula to scan the GL, find each
unique Vendor name in column A, and copy the data from each row, creating a
block of data for each vendor.

Basically, find, sort and copy onto separate sheets all the row data for
each unique Vendor. I tried using lookup, match and other array functions,
but they don't seem to help much, especially because of the restrictions
under the criteria input (such as a date range which I really wish it would
do so I could sort entries by month or year).

I realize I could do this by searching for each Vendor by name entered
manually into each formula, but I'm hoping to make the it generic by finding
the various vendor names itself from the GL entries.

Thanks again for your help!

Tim


"Ron Coderre" wrote:

Actually, what I posted was just the VBA way to do an Excel Advanced Filter...

Using my previous example:
If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
A2:A20 (your list of values)
C1: Field1

Select A1:A20
DataFilterAdvanced Filter
Check: Copy to another location
Copy To: C1
Check: Unique Values
Click [OK]

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Is that more what you were looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

Thanks for your quick response. Perhaps I posted this in the wrong section
because this is obviously "C" Language. I learned BASIC and assembler, but
not "C". I can teach myself to do this but I was hoping that there was a way
to do this with basic functions in Excel. Is there a cut and paste way to
insert this code into Excel?

Thanks Again,
Tim


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

This might be a lot less work than using all formulas.....
Have you considered a Pivot Table?

If you have Vendor names down one column and you have column headings for
the data area, the Pivot Table will automatically list each Vendor
(alphabetically) and display totals by category. The pivot table would also
let you hide/show specific vendors.

If you think you might want to know more...check these websites:

http://peltiertech.com/Excel/Pivots/pivottables.htm

http://datapigtechnologies.com/ExcelMain.htm
(this site contains free online video tutorials)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

This is what I get for asking complex questions late at night. ;)
I'll try to be less vague and more to the point.
I'm creating a general ledger for my personal finances. I would like to have
another sheet programmed with a function/formula to scan the GL, find each
unique Vendor name in column A, and copy the data from each row, creating a
block of data for each vendor.

Basically, find, sort and copy onto separate sheets all the row data for
each unique Vendor. I tried using lookup, match and other array functions,
but they don't seem to help much, especially because of the restrictions
under the criteria input (such as a date range which I really wish it would
do so I could sort entries by month or year).

I realize I could do this by searching for each Vendor by name entered
manually into each formula, but I'm hoping to make the it generic by finding
the various vendor names itself from the GL entries.

Thanks again for your help!

Tim


"Ron Coderre" wrote:

Actually, what I posted was just the VBA way to do an Excel Advanced Filter...

Using my previous example:
If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
A2:A20 (your list of values)
C1: Field1

Select A1:A20
DataFilterAdvanced Filter
Check: Copy to another location
Copy To: C1
Check: Unique Values
Click [OK]

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Is that more what you were looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

Thanks for your quick response. Perhaps I posted this in the wrong section
because this is obviously "C" Language. I learned BASIC and assembler, but
not "C". I can teach myself to do this but I was hoping that there was a way
to do this with basic functions in Excel. Is there a cut and paste way to
insert this code into Excel?

Thanks Again,
Tim


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

Excellent! Thank you so very much!

Tim

"Ron Coderre" wrote:

This might be a lot less work than using all formulas.....
Have you considered a Pivot Table?

If you have Vendor names down one column and you have column headings for
the data area, the Pivot Table will automatically list each Vendor
(alphabetically) and display totals by category. The pivot table would also
let you hide/show specific vendors.

If you think you might want to know more...check these websites:

http://peltiertech.com/Excel/Pivots/pivottables.htm

http://datapigtechnologies.com/ExcelMain.htm
(this site contains free online video tutorials)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

This is what I get for asking complex questions late at night. ;)
I'll try to be less vague and more to the point.
I'm creating a general ledger for my personal finances. I would like to have
another sheet programmed with a function/formula to scan the GL, find each
unique Vendor name in column A, and copy the data from each row, creating a
block of data for each vendor.

Basically, find, sort and copy onto separate sheets all the row data for
each unique Vendor. I tried using lookup, match and other array functions,
but they don't seem to help much, especially because of the restrictions
under the criteria input (such as a date range which I really wish it would
do so I could sort entries by month or year).

I realize I could do this by searching for each Vendor by name entered
manually into each formula, but I'm hoping to make the it generic by finding
the various vendor names itself from the GL entries.

Thanks again for your help!

Tim


"Ron Coderre" wrote:

Actually, what I posted was just the VBA way to do an Excel Advanced Filter...

Using my previous example:
If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
A2:A20 (your list of values)
C1: Field1

Select A1:A20
DataFilterAdvanced Filter
Check: Copy to another location
Copy To: C1
Check: Unique Values
Click [OK]

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Is that more what you were looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

Thanks for your quick response. Perhaps I posted this in the wrong section
because this is obviously "C" Language. I learned BASIC and assembler, but
not "C". I can teach myself to do this but I was hoping that there was a way
to do this with basic functions in Excel. Is there a cut and paste way to
insert this code into Excel?

Thanks Again,
Tim


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

If you want the solution to be on-going rather than one-time, you could
also use any of the methods suggested in this thread today

http://www.excelforum.com/showthread.php?t=495448

The method I suggested works without VBA.

HTH

Declan O'R


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=495405

find every different text or data in a column - for Excel
 

Ron, your code is very helpful compared to using the extract unique formulas
which is slow and memory intensive. If you have the time, could you amend
your code for me to sort the extracted list in ascending order.
Thank you
Robert


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

Heres something I hope you can work with....

Option Explicit
Sub ExtractUniqueAndSort()
With Sheets("Sheet1")
.Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("C1"), _
Unique:=True

.Range(.Range("C1"), .Range("C1").End(xlDown)) _
.Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
End Sub


***********
Regards,
Ron

XL2002, WinXP-Pro


"Robert" wrote:

Ron, your code is very helpful compared to using the extract unique formulas
which is slow and memory intensive. If you have the time, could you amend
your code for me to sort the extracted list in ascending order.
Thank you
Robert


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?

find every different text or data in a column - for Excel
 

Hi Ron:

Your code captures the general principle of what I am looking for, however,
I need to list unique date from several list of data on multiple spreadsheets
and copy this data to a separate worksheet.

To be exact, I have 32 spreadsheets, one summary sheet and 31 detail data
sheets for each day of the month. Daily entries on the 31 sheets may be
repeated for a client but on the summary sheet I need the client to be listed
only once.

Please advise how I may modify your code below to accomplish this.

Please note also that when I executed the program with your code, the first
value in my list was repeated once (i.e. there were two occurances of that
value). I rectified this by changing "Header:=xlYes" to "Header:=xlNo".
Kindly advise whether this was the correct approach.

Thank you!
--
BG


"Ron Coderre" wrote:

Heres something I hope you can work with....

Option Explicit
Sub ExtractUniqueAndSort()
With Sheets("Sheet1")
.Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("C1"), _
Unique:=True

.Range(.Range("C1"), .Range("C1").End(xlDown)) _
.Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
End Sub


***********
Regards,
Ron

XL2002, WinXP-Pro


"Robert" wrote:

Ron, your code is very helpful compared to using the extract unique formulas
which is slow and memory intensive. If you have the time, could you amend
your code for me to sort the extracted list in ascending order.
Thank you
Robert


"Ron Coderre" wrote:

Something to get you started...

If your list is in Cells A1:A20 on Sheet1 with
A1: Field1
C1: Field1

Option Explicit
Sub ExtractUnique()
Sheets("Sheet1").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

That will extract all unique values from the Field1 range into the a new
range beginning in Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Space Elf" wrote:

I have rows of data that I would like to the program to pick through for
sorting. By using one column, I would like to find / make a list of each
different text or data going down that column. Is this possible?