Home

Using index match to list SKUs from planogram

%3CLINGO-SUB%20id%3D%22lingo-sub-897686%22%20slang%3D%22en-US%22%3EUsing%20index%20match%20to%20list%20SKUs%20from%20planogram%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897686%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20there%20are%20many%20posts%20that%20talked%20about%20similar%20issues%20but%20most%20of%20the%20time%20people%20are%20just%20posting%20the%20fix%20and%20not%20what%20the%20fix%20does%20or%20how%20it%20can%20be%20adapted.%20With%20that%20being%20said%20what%20i%20am%20looking%20to%20do%20from%20the%20attached%20workbook%20is%20to%20take%20each%20individual%20sku%20from%20the%20different%20planogram%20sheets%20(%3CEM%3ENWX%20SAMSUNG%2C%20NWX%20IPHONE%2C%20SSD%20SAMSUNG%2C%20SSD%20IPHONE%3C%2FEM%3E)%20and%20have%20then%20listed%20in%20column%20%22A%22%20(%3CEM%3ESKU%3C%2FEM%3E)%20on%20the%20%3CEM%3EORDERING%3C%2FEM%3E%20sheet%20all%20while%20ignoring%20duplicates.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20fighting%20with%20this%20for%20over%20a%20week%20now%20and%20i%20am%20giving%20up%20and%20asking%20for%20help.%20I%20have%20been%20able%20to%20get%20it%20to%20return%201%20column%20(or%20row)%20from%201%20sheet%2C%20but%20it's%20refusing%20to%20give%20me%20every%20sku%20from%20even%201%20planogram%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-897686%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898437%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20index%20match%20to%20list%20SKUs%20from%20planogram%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F149914%22%20target%3D%22_blank%22%3E%40katrina%20bethea%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recommend%20that%20you%20normalize%20the%20layout%20of%20your%20data%2C%20such%20that%20for%20each%20sheet%2C%20your%20column%20labels%20may%20be%20Part%2C%20UPC%2C%20Case%2C%20Price%2C%20and%20Image.%20Thereafter%2C%20attach%20your%20normalized%20file%20and%20I'll%20see%20what%20I%20can%20do%20to%20return%20your%20desired%20results.%20Please%20avoid%20merging%20cells.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899708%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20index%20match%20to%20list%20SKUs%20from%20planogram%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Ball%20the%20SKUs%2C%20on%20hand%2C%20prices%2C%20ect%20are%20in%20a%20standard%20list%20format%20on%20the%20%22PEXTRACT%22%20tab.%20this%20is%20what%20is%20pulled%20from%20our%20back%20office%20reports%20for%20all%20product%20information.%20The%20goal%20is%20that%20if%20i%20am%20not%20here%20to%20handle%20the%20planograms%20a%20person%20could%20simply%20pull%20a%20report%20and%20paste%20it%20in%20that%20tab%20to%20get%20the%20order%20numbers%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20doesn't%20have%20to%20be%20an%20index%20match%2C%20that%20is%20just%20what%20i%20thought%20i%20needed.%20The%20planograms%20have%20to%20stay%20within%20the%20format%20they%20are%20because%20that%20is%20what%20is%20printed%20to%20pdf%20and%20sent%20to%20each%20store%20so%20they%20can%20set%20up%20their%20walls%20(and%20that's%20the%20way%20the%20higher%20ups%20want%20it%20laid%20out%20as%20well).%20What%20i%20am%20essentially%20doing%20is%20cross%20checking%20the%20%22PEXTRACT%22%20to%20the%20planogram%20and%20pasting%20the%20%22true%22%20result%20skus%20(without%20duplicates)%20in%20column%20A%3AA%20of%20the%20ordering%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20still%20isn't%20a%20way%20to%20get%20the%20needed%20information%20with%20those%20tabs%20i%20will%20start%20working%20on%20a%20UDF%20that%20will%20do%20what%20i%20need.%20I%20just%20didn't%20want%20to%20have%20to%20go%20that%20route%20since%20UDF's%20always%20run%20into%20problems%20when%20the%20workbook%20is%20sent%20to%20another%20PC%20in%20my%20experience.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
katrina bethea
Contributor

I know there are many posts that talked about similar issues but most of the time people are just posting the fix and not what the fix does or how it can be adapted. With that being said what i am looking to do from the attached workbook is to take each individual sku from the different planogram sheets (NWX SAMSUNG, NWX IPHONE, SSD SAMSUNG, SSD IPHONE) and have then listed in column "A" (SKU) on the ORDERING sheet all while ignoring duplicates. 

 

I've been fighting with this for over a week now and i am giving up and asking for help. I have been able to get it to return 1 column (or row) from 1 sheet, but it's refusing to give me every sku from even 1 planogram sheet.

2 Replies

@katrina bethea 

I recommend that you normalize the layout of your data, such that for each sheet, your column labels may be Part, UPC, Case, Price, and Image. Thereafter, attach your normalized file and I'll see what I can do to return your desired results. Please avoid merging cells. 

@Twifoo all the SKUs, on hand, prices, ect are in a standard list format on the "PEXTRACT" tab. this is what is pulled from our back office reports for all product information. The goal is that if i am not here to handle the planograms a person could simply pull a report and paste it in that tab to get the order numbers needed.

 

The formula doesn't have to be an index match, that is just what i thought i needed. The planograms have to stay within the format they are because that is what is printed to pdf and sent to each store so they can set up their walls (and that's the way the higher ups want it laid out as well). What i am essentially doing is cross checking the "PEXTRACT" to the planogram and pasting the "true" result skus (without duplicates) in column A:A of the ordering sheet.

 

If there still isn't a way to get the needed information with those tabs i will start working on a UDF that will do what i need. I just didn't want to have to go that route since UDF's always run into problems when the workbook is sent to another PC in my experience.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies