Creating a new list based on conditions

**Dubbelito** · 06-04-2008, 11:50 AM

Hi, first time poster here. I have a predicament that gives me extra headache every month, so I was wondering if I couldn't work around it by using some smart solution, and I was hoping to find it here...

This is the situation:
I have a list of between 10 000 - 20 000 rows, with different part numbers that are classified, and I would like to populate a new list using the info in that list but only the ones classified with an "A" in one list, "B" in another and so on.

I want to be in cell A1, use this formula, and the first part number (which is the unique no that I then can do a Lookup with) with classification "A" should pop up. I can then fill the series until the list is complete.

Today I have to Autofilter, copy - paste every one, which is really tedious..

I have attached a simple example which shows what I mean...

Best regards and thanks in advance,

Dubbelito

**Ron Coderre** · 06-04-2008, 12:19 PM

Would you consider using a Pivot Table?

Using your posted workbook...

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

PAGE: Drag the CRITERIA field here

ROW: Drag the PARTNUM and VALUE fields here
(Dbl-click each and set Subtotals to None)

COLUMN: (Leave this empty)

DATA: Drag the VALUE field here, too.
(Dbl-click it and set Subtotals to COUNT)

Click [OK]
Select where you want the Pivot Table…Click [Finish].

That will list each unique combination of
PARTNUM and VALUE and the count of each.

If you click the CRITERIA dropdown and select "A",
only those records will display.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?

For more info on Pivot Tables:
http://www.nickhodge.co.uk/gui/datam...ablereport.htm
http://www.contextures.com/tiptech.html

**reggie1000** · 06-04-2008, 12:53 PM

Hi Dubbelito,

i have attached your example, in order to list criteria "A", i've used a formula that i got from this forum, the formula give a unique rank subject to 2 columns, by concatenating the criteria "A" and the rank this gives an easy to use Vlookup table,

hope it helps,

thanks reg

**modytrane** · 06-04-2008, 01:55 PM

Take a look at the example attached.
Select a criteria in Cell C32.
I beleive it does what you want.
Good Luck
modytrane

**Dubbelito** · 06-05-2008, 03:58 AM

Thank you all three for your quick replies! In this case I must say that it was Modytrane who hit the bull's eye! I took a quick look at it and it seems to be exactly what I wanted!

Reggie1000, your tip was also good and I will definetly add those formulas to my arsenal.

Ron, the Pivot table is also good, though I don't use it so much. Dunno why, just don't like it as much as a normal table.

Again, thank you very much!

Best regards,
Dubbelito