Condense data for stock ordering

S

Stevet

Hi all,
Can anybody help with this problem, I have column
A and B in another worksheet and I want to condense the list to only the
information in columns D and E. I have the formula for column E (thanks to
Luke), I now just need the formula for column D. Incidently, the formula in
column E is =IF(D2="","",(SUMIF($A$2:$A$12,D2,$B$2:$B$12)))

A B D E
Fabric Quantity Fabric Quantity
Chenille 10 Chenille 230
Chenille 20 Pampas 200
Chenille 30 Aster 230
Pampas 40
Pampas 50
Aster 60
Aster 70
Chenille 80
Chenille 90
Aster 100
Pampas 110
 
A

Ashish Mathur

Hi,

To get all the unique fabric types in column D, you can use advanced filters

1. Go to Data > Filter > Advanced Filter
2. In the report type select "Copy to another location"
3. In the list range , select A1:A12 (assuming that the heading is in row
1)
4. Leave the criteria range blank;
5. In the copy to box, select D1:D12
6. Check the box for unique records only
7. Click on OK

This will get you all the unique fabric types in column D.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
J

Jacob Skaria

Hi Steve

Try these formulas in D2 and E2
--Put headers in D1 and E1

--In D2..(Array entered).. dont just enter; use CTRL+SHIFT+ENTER to apply
the formula
=IF(MIN(IF(ISNA(MATCH($A$1:$A$12,$D$1:D1,0)),ROW($A$1:$A$12))),
INDEX($A$1:$A$12,MIN(IF(ISNA(MATCH($A$1:$A$12,$D$1:D1,0)),
ROW($A$1:$A$12)))),"")
'Edit the range A12 to suit your requirement

--In E2
=IF(D2="","",SUMIF(A:A,D2,B:B))

If this post helps click Yes
 
S

Stevet

Thankyou Jacob, That was exactly what I was looking for.
I don't know what half of it means, but I will endeaver to understand the
logic behind it, I just don't have the time at the moment.
Once again, thanks very much.
 
S

Stevet

Thanks for your help Ashish, unfortunately that was not quite what I wanted.
--
Kind regards,
Stevet


Ashish Mathur said:
Hi,

To get all the unique fabric types in column D, you can use advanced filters

1. Go to Data > Filter > Advanced Filter
2. In the report type select "Copy to another location"
3. In the list range , select A1:A12 (assuming that the heading is in row
1)
4. Leave the criteria range blank;
5. In the copy to box, select D1:D12
6. Check the box for unique records only
7. Click on OK

This will get you all the unique fabric types in column D.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top