auto list data

**choy96** · 03-20-2010, 02:23 AM

i need a formula can auto list out the data.

thks

sorry, Paul

Item---Color---Qtty
Dress--Red-------1
Dress--Blue------1
Shirt----Grey-----1
Dress--Blue------1
Cap-----Yellow---1

The table above got different item and its color, i need a formula that can help me list out dress, shirt and cap. The color always is the same i.e red, blue, grey, yellow, but item sometime will have new item such as skirt or anything else. table as below:

------------------------------Red----------------Blue----------------Grey---------------Yellow-----
item Formula-----Qtty Formula---Qtty Formula---Qtty Formula---Qtty Formula---
item Formula-----Qtty Formula---Qtty Formula---Qtty Formula---Qtty Formula---
item Formula-----Qtty Formula---Qtty Formula---Qtty Formula---Qtty Formula---

hope understand. thanks.

**Paul** · 03-20-2010, 02:34 AM

Hello Choy, welcome to the forum.

Please take a moment to provide details in the thread text rather than stating what you need and uploading a workbook. Having the workbook is nice, but your request is rather vague, so a clear description of your problem will help you get answers more quickly without requiring people to open your spreadsheet to find out they don't know how to help.

**johncena** · 03-20-2010, 03:32 AM

Use Filter Function.
1. Click on first list's column
2. Goto Data > Filter > Advanced Filter. Click on Copy Elsewhere.
3. Click Range from first list
4. Leave Criteria Range EMPTY!!!!
5. Empty Copy To range field
6. Click Unique Records
7.Click Okay

**Palmetto** · 03-20-2010, 09:14 AM

Use Filter Function.

Just to be clear - this is referring to Advanced Filter not Auto Filter

You may find this link helpful on understanding How to Create a Unique List of Items using Advanced Filter

**choy96** · 03-21-2010, 12:26 AM

Originally Posted by Palmetto

Just to be clear - this is referring to Advanced Filter not Auto Filter

You may find this link helpful on understanding How to Create a Unique List of Items using Advanced Filter

why i cant get? The list i get is dress, dress, shirt, cap.
below is the setting in Advance filter:
-click button copy to another location
-list range:$B$3:$B$7
-Copy to:$B$11:$B$13

what mistake i have done ?

**teylyn** · 03-21-2010, 12:34 AM

the first dress line is for red, the second dress line is for blue. These rows are not unique in your source data if you select from column B to column D.

What you really require is a pivot table. It will do the grouping and summing you require. See attached.

**choy96** · 03-24-2010, 05:50 AM

if item name change as below:
DRESS = AD123FGAC
SHIRT = AG123FBAC
CAP =AD123FEAC

i want subtotal the FG, FB, FE depend on colour, i trying using =SUM(IF((B24:B28="*"&B31&"*")*(C24:C28=C31),D24:D28)) but get the wrong answer.

please help.

**teylyn** · 03-24-2010, 07:06 AM

Hi,

you're mixing up where to use the wildcards. That won't wash. B24:B28 is where you have more text than the text you're looking for. Using wildcards on your search term won't help that.

Try this on your pianola: In C32

=SUM(IF(ISNUMBER(SEARCH($B32,$B$24:$B$28))*($C$24:$C$28=C$31),$D$24:$D$28))

This is an array formula, confirm with Ctrl-Shift-Enter

then copy down and across.