Hey,
I have materials that are located in 3 plants. When i pull my report it pulls each material in each plant. I need to condense my duplicated materials down to one and still show if it is available at the plants. See the attached spreadsheet and let me know if this is possible.Sample Spreadsheet.xlsx
=IFERROR(INDEX(B$3:B$12,MATCH(1,($A$3:$A$12=$A18)*(B$3:B$12="Y"),0)),"N")
Array formula: Press Ctrl+Shift+Enter, not just Enter
1) erase the "example results" below the real data
2) Put this formula in E3:
=IF($A3=$A4, IF(OR(B3="y", B4="y", E4="y"), "Y", "N"), B3)
3) Copy that formula down the data set and across through to column G, we're creating a new set of Y/N answers.
4) Put this formula in H3, then copy it down:
=IF(A3=A2, "delete", "keep")
5) Highlight columns E:H, Copy them, do a PASTE SPECIAL > VALUES to remove the formulas.
6) Copy B2:D2 onto E2:G2 to transfer the titles
7) Delete columns B:D
8) Highlight column E and turn on the DATA > FILTER.
9) Filter for the value "delete"
10) Delete all visible rows
11) Delete column E
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
The IFERROR is not working, the second solution will not scale due to the number of materials/duplicate materials i have. Any more suggestions?
It won't scale? Present a complete set of data and we'll present other ideas or adjust the ones given already.
Got the formula to work. thank you for your help.
I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks