Listing Different Values

[jeffmoseler]

I need code or formulas to take a column and create a list of different values. ie:

aaa
bbb
bbb
ccc
ddd
eee
eee

would look like:

aaa
bbb
ccc
ddd
eee

Is there something available for that?

 

[fairwinds]

Hi,

Try advanced filter and filter for unique records only. ( And why not record a macro while doing so.)

 

[Aladin Akyurek]

I need code or formulas to take a column and create a list of different values. ie:

aaa
bbb
bbb
ccc
ddd
eee
eee

would look like:

aaa
bbb
ccc
ddd
eee

Is there something available for that?

Book8
	A	B	C	D
1		0		5
2	Item	Idx		Distinct Item List
3	aaa	1		aaa
4	bbb	2		bbb
5	bbb			ccc
6	ccc	3		ddd
7	ddd	4		eee
8	eee	5
9	eee
Sheet1

B1 must house a 0.

B3, copied down:

=IF(A3<>"",IF(ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$B$1:B2)+1,""),"")

D1:

=LOOKUP(9.99999999999999E+307,B1:B9)

D3, copied down:

=IF(ROW()-ROW($D$3)+1<=$D$1,LOOKUP(ROW()-ROW($D$3)+1,$B$3:$B$9,$A$3:$A$9),"")

 

[Lakelands01]

From the excel help files, this may be what your looking for:

Select the column or click a cell in the range or list you want to filter.
On the Data menu, point to Filter, and then click Advanced Filter.
Do one of the following.
To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place.
To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference.
To select a cell, click Collapse Dialog to temporarily hide the dialog box. Select the cell on the worksheet, and then press Expand Dialog .

Select the Unique records only check box.
Note Once you filter for unique values, you can copy them to another worksheet and then archive or delete the original worksheet.

 

[jeffmoseler]

Good Call

But here's my problem. I can't filter data from one Sheet to a location on another sheet.

I'm copying and pasting a quickbooks report into one sheet. If I create an advanced sort in that sheet it will be over written when I copy in the new data. So I need the Sort function to happen on another sheet so when the data is copied in, the other sheet will update.

I have a feeling I new VBA to do this, I am a realtive novice to VBA though.

 

[Norie]

Re: Good Call

But here's my problem. I can't filter data from one Sheet to a location on another sheet.

Yes you can but you do need VBA.

So why not try recording a macro as Fairwinds suggested.

 

[XLGibbs]

So if advanced filter does not suit, do you need to be appending data to the second of these two sheets? or do you copy in the data at the bottom of the 1st page and Then want it to sort?

Your first example was pretty off the mark considering you are now talking filtering, copying and appending the data to a new location...

There are lots of examples of searching, finding, and copying rows from one sheet to the next, or even multiple sheets within this board, including recent posts....

 

[fairwinds]

The formulas that Alain suggested works between sheets.

Also if you record a macro doing autofilter you could just continue recording while copy paste into the new sheet and delete the old data.

If it does not work, just post the recorded code here and we can tidy it up and make it more general.