| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
another interesting thing...
Maybe there's an easy way to do this. A lot of my excel work revolves
around data pulled from a live database. I'll have variable length tables. My current issue has to do with a set of information as below. (prepare for scrolling). ------------ SNIP ------------ 07 Natural Remedies 8842 10-10 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 10-19-05CR 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 Select Nutrition 652686 10 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527CR 10-17-05 07 Now 568527CR 10-17-05 07 Now 568527CR 10-17-05 07 Natures Way 91045097 10-19 07 Nature's Way 91045114 10-19 07 Nature's Way 91045114 10-19 07 Nature's Way 91045114 10-19 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05CR 07 United 677582 10-26-05CR ------------ SNIP ------------ What I'd like to do is take this column from sheet 1 and have the data autofiltered to sheet 2 so it appears as such, removing repetitions: ------------ SNIP ------------ 07 Natural Remedies 8842 10-10 07 Nature's Way 91045114 10-19 07 Natures Way 91045097 10-19 07 Now 568527 10-17-05 07 Now 568527CR 10-17-05 07 Nutraceutical 3220889.1 10- 07 Renew Life 10253332 10-11-0 07 Sami's Bakery 14103 10-18- 07 Select Nutrition 652686 10 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 CR 07 United 677582 10-26-05CR 07 United 649464 07 United 649464 10-19-05CR 07 United 677582 10-26-05 ------------ SNIP ------------ The trick is, I want to see it happen automatically, and when the first sheet refreshes, I'd like to have the 2nd sheet automatically refresh also. Any information is helpful. Thanks in advance - you folks are always a great source of information. O.B.D. Ben/ND |
|
#2
|
|||
|
|||
|
another interesting thing...
"Nick Dangr" wrote in message
ups.com... Maybe there's an easy way to do this. A lot of my excel work revolves around data pulled from a live database. I'll have variable length tables. My current issue has to do with a set of information as below. (prepare for scrolling). ------------ SNIP ------------ 07 Natural Remedies 8842 10-10 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 10-19-05CR 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 Select Nutrition 652686 10 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527CR 10-17-05 07 Now 568527CR 10-17-05 07 Now 568527CR 10-17-05 07 Natures Way 91045097 10-19 07 Nature's Way 91045114 10-19 07 Nature's Way 91045114 10-19 07 Nature's Way 91045114 10-19 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05CR 07 United 677582 10-26-05CR ------------ SNIP ------------ What I'd like to do is take this column from sheet 1 and have the data autofiltered to sheet 2 so it appears as such, removing repetitions: ------------ SNIP ------------ 07 Natural Remedies 8842 10-10 07 Nature's Way 91045114 10-19 07 Natures Way 91045097 10-19 07 Now 568527 10-17-05 07 Now 568527CR 10-17-05 07 Nutraceutical 3220889.1 10- 07 Renew Life 10253332 10-11-0 07 Sami's Bakery 14103 10-18- 07 Select Nutrition 652686 10 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 CR 07 United 677582 10-26-05CR 07 United 649464 07 United 649464 10-19-05CR 07 United 677582 10-26-05 ------------ SNIP ------------ The trick is, I want to see it happen automatically, and when the first sheet refreshes, I'd like to have the 2nd sheet automatically refresh also. Any information is helpful. Thanks in advance - you folks are always a great source of information. O.B.D. Ben/ND Then you want to simply eliminate duplicates on a single column. No other operation. Any kind of Sorting after that? Bruno |
|
#3
|
|||
|
|||
|
another interesting thing...
Nick Dangr wrote...
.... What I'd like to do is take this column from sheet 1 and have the data autofiltered to sheet 2 so it appears as such, removing repetitions: .... The trick is, I want to see it happen automatically, and when the first sheet refreshes, I'd like to have the 2nd sheet automatically refresh also. .... Assuming your source data is in a single column, and the range containing that data were named D, then you could do this with formulas. If the topmost result cell were A1 in another worksheet, try the following array formulas. A1 [array formula]: =INDEX(D,MATCH(0,COUNTIF(D,"<"&D),0)) A2 [array formula]: =IF(SUM(COUNTIF(D,A$1:A1))<COUNTA(D), INDEX(D,MATCH(COUNTIF(D,"<="&A1),COUNTIF(D,"<"&D), 0)),"") Fill A2 down as needed. Note: such formulas are inefficient. Filtering and sorting would be more efficient. It'd be better to use a Calculate event handler to run an advanced filter against the range D, copying it's distinct records to another location, then sorting the result. |
|
#4
|
|||
|
|||
|
another interesting thing...
For come odd reason I can't figure out the sort part, but I got this far for ya. Place this VB code in the worksheet that gets its input from the Database and name the sheet with the filtering "Filter" Code: -------------------- Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Sheets("Filter").Range("A:A").Clear Dim cell As Variant Dim Oldval As Variant Dim NewVal As Variant Dim i As Long i = 1 NewVal = vbNullString For Each cell In ActiveSheet.Range("A:A") If cell = vbNullString Then Exit For Oldval = cell.Value If NewVal < Oldval Then NewVal = Oldval Sheets("Filter").Cells(i, 1).Value = NewVal i = i + 1 End If Next cell End Sub -------------------- -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=479914 |
|
#5
|
|||
|
|||
|
another interesting thing...
As always you guys have a lot of useful input - I'm gonna take what
you've said thus far and play around. If you think of anything else, feel free to follow up - I have this one "starred" to watch ;-) O.B.D. |
|
#6
|
|||
|
|||
|
another interesting thing...
On 27 Oct 2005 11:53:38 -0700, "Nick Dangr" wrote:
Maybe there's an easy way to do this. A lot of my excel work revolves around data pulled from a live database. I'll have variable length tables. My current issue has to do with a set of information as below. (prepare for scrolling). ------------ SNIP ------------ 07 Natural Remedies 8842 10-10 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 Renew Life 10253332 10-11-0 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 07 United 649464 10-19-05CR 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 Nutraceutical 3220889.1 10- 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 TOL 103943768 10-19-05 CR 07 Select Nutrition 652686 10 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Sami's Bakery 14103 10-18- 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527 10-17-05 07 Now 568527CR 10-17-05 07 Now 568527CR 10-17-05 07 Now 568527CR 10-17-05 07 Natures Way 91045097 10-19 07 Nature's Way 91045114 10-19 07 Nature's Way 91045114 10-19 07 Nature's Way 91045114 10-19 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05 07 United 677582 10-26-05CR 07 United 677582 10-26-05CR ------------ SNIP ------------ What I'd like to do is take this column from sheet 1 and have the data autofiltered to sheet 2 so it appears as such, removing repetitions: ------------ SNIP ------------ 07 Natural Remedies 8842 10-10 07 Nature's Way 91045114 10-19 07 Natures Way 91045097 10-19 07 Now 568527 10-17-05 07 Now 568527CR 10-17-05 07 Nutraceutical 3220889.1 10- 07 Renew Life 10253332 10-11-0 07 Sami's Bakery 14103 10-18- 07 Select Nutrition 652686 10 07 TOL 103943768 10-19-05 07 TOL 103943768 10-19-05 CR 07 United 677582 10-26-05CR 07 United 649464 07 United 649464 10-19-05CR 07 United 677582 10-26-05 ------------ SNIP ------------ The trick is, I want to see it happen automatically, and when the first sheet refreshes, I'd like to have the 2nd sheet automatically refresh also. Any information is helpful. Thanks in advance - you folks are always a great source of information. O.B.D. Ben/ND Well, you did say you wanted automatic updating. Here's one way that should work. I am assuming all your data is in a single column. 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ 2. On sheet 2, select a one column range that is long enough to accommodate all of the unique entries from sheet 1. It is OK to leave a number of blanks at the end to allow for expansion. I selected A1:A150 3. Assuming your data begins in Sheet1!A1, and you want your results to start in Sheet2!A1, enter into the formula bar: =UNIQUEVALUES(Sheet1!A1:An) where n= the maximum row number that you have selected on sheet2. 'n' can be as great as 65535. 4. After typing it in and with the range on sheet2 still selected, hold down <ctrl<shift while hitting <enter. Excel will enter this as an array formula in the entire selection. In each cell, the formula will appear the same, and with braces {...} around the formula. 5. The results will be sorted in descending order. If you want them sorted in ascending order, there is an optional argument to the function: e.g. =UNIQUEVALUES(Sheet1!A1:A65535,1) Since this is a volatile function, any changes made in sheet 1 will be reflected in sheet 2 without further intervention. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Interesting Counting Question...HELP | Excel Discussion (Misc queries) | |||
| How do I get one cell to type the same thing as another cell? | Excel Discussion (Misc queries) | |||
| Is there such a thing... | Excel Discussion (Misc queries) | |||
| Lengthy and weird, but interesting | Excel Worksheet Functions | |||
| Strange thing happened with TOC | Excel Discussion (Misc queries) | |||
Linear Mode
