Condense data for stock ordering

  • Thread starter Thread starter Stevet
  • Start date Start date
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
 
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
 
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
 
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.
 
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
Click to expand...
 
Back
Top