Condense data for stock ordering
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 -- Kind regards, Stevet -- Kind regards, Stevet |
Condense data for stock ordering
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 "Stevet" wrote in message ... 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 -- Kind regards, Stevet -- Kind regards, Stevet |
Condense data for stock ordering
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 --------------- Jacob Skaria "Stevet" wrote: 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 -- Kind regards, Stevet -- Kind regards, Stevet |
Condense data for stock ordering
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. -- Kind regards, Stevet "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Stevet" wrote: 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 -- Kind regards, Stevet -- Kind regards, Stevet |
Condense data for stock ordering
Thanks for your help Ashish, unfortunately that was not quite what I wanted.
-- Kind regards, Stevet "Ashish Mathur" wrote: 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 "Stevet" wrote in message ... 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 -- Kind regards, Stevet -- Kind regards, Stevet |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com