I need help building a formula that will capture all the items in a list that match a certain criteria. I have a list of materials on a tab and their corresponding SKU. I need to list all the items that match a certain sku (i.e. Everything that is a C, D, or CORE). I've listed an example template where I would like to fill in the yellow highlighted box with all the potential sku's based on what's listed in cell C7. Thanks for all the help!
Put this in B11...
=IFERROR(INDEX(Materials!$D$2:$D$27,SMALL(IF((Materials!$D$2:$D$27={"C","D","CORE"}),ROW(Materials!$A$2:$A$27)-1),ROWS(Materials!$A$1:A1))),"")
and this in C11....
=IFERROR(INDEX(Materials!$C$2:$C$27,SMALL(IF((Materials!$D$2:$D$27={"C","D","CORE"}),ROW(Materials!$A$2:$A$27)-1),ROWS(Materials!$A$1:A1))),"")
Both of these are ARRAY formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Copy them both down as far as needed
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
With a little code, starts after change value of C7
Kind regards
Leo
Hi there, Leo I am unable to view your code and therefore can't see how it was done to replicate myself. Can you help?
FDibbins: I would like to be able to adjust cell C7 to be any combination of A,B,C and Core and then have the corresponding materials populate.
Then you will need to put them in their own cells, say in Summary E2:E7
Becasue I am can only use that formula with the SKU's hard coded, I have now taken a different approach for this, using a helper column.
1. In materials E2, copy this down (you can hide it if you want)...
=IF(ISERROR(MATCH(D2,Summary!$E$3:$E$7,0)),Materials!E1,Materials!E1+1)
Then in Summary...
B12=IFERROR(INDEX(Materials!$D$2:$D$27,MATCH(ROW(A1),Materials!$E$2:$E$27,0)),"")
C12=IFERROR(INDEX(Materials!$C$2:$C$27,MATCH(ROW(A1),Materials!$E$2:$E$27,0)),"")
both copied down as needed
This will allow you to enter up to 5 codes
If you insist on keeping the different SKU's in one cell, check out the attachment.
In cell B12 (of Summary) the arrayformula (ctrl+shift+enter), drag down to B33
=IFERROR(INDEX(Materials!$D$2:$D$27,SMALL(IF(ISNUMBER(SEARCH(Materials!$D$2:$D$27,Summary!$C$7)),ROW($D$2:$D$27)-1,"");ROWS($1:1))),"")
b12=IFERROR(INDEX(Materials!$D:$D,SMALL(INDEX((MMULT((Materials!$D$2:$D$27=TRIM(MID(SUBSTITUTE($C$7,",",REPT(" ",100)),(COLUMN(INDIRECT("a1:j1"))-1)*100+1,100)))*1,ROW(INDIRECT("a1:a10")))=0)*10^10+ROW(Materials!$D$2:$D$27),0),ROW($A1))),"")c12=IFERROR(INDEX(Materials!$C:$C,SMALL(INDEX((MMULT((Materials!$D$2:$D$27=TRIM(MID(SUBSTITUTE($C$7,",",REPT(" ",100)),(COLUMN(INDIRECT("a1:j1"))-1)*100+1,100)))*1,ROW(INDIRECT("a1:a10")))=0)*10^10+ROW(Materials!$D$2:$D$27),0),ROW($A1))),"")
Please Login or Register to view this content.Try this and copy towards downPlease Login or Register to view this content.
It will work up to 10 SKUs in Cell "C7"
SEE ATTACHED FILE
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
These are great! thank you. I would need to adjust to accommodate additional brands.
Hi Coda,
code is behind sheet Summery in Worksheet_Change Event
something like this
Kind regardsPlease Login or Register to view this content.
Leo
Thanks Leo ... although I'm a little shamed to say I don't know much about coding.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks