SOLVED

formula for managing products for website store

%3CLINGO-SUB%20id%3D%22lingo-sub-2252936%22%20slang%3D%22en-US%22%3Eformula%20for%20managing%20products%20for%20website%20store%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20would%20like%20you%20to%20help%20me%20with%20the%20formula%20that%20I%20am%20looking%20for%20I%20wanna%20a%20formula%20that%20will%20allow%20to%20me%20comparing%20date%20and%20value%20(like%20products%2C%20SKU%20products%20model%20)%20with%20the%207%20sheets%20and%20get%20me%20the%20matched%20value%20and%20lowest%20price%20and%20sheet%20name%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20attached%20a%20sample%26nbsp%3Bof%20my%20worksheet%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image001.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269397i3D887B7AE0FC0122%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image001.png%22%20alt%3D%22image001.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2252936%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2253045%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20managing%20products%20for%20website%20store%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1016587%22%20target%3D%22_blank%22%3E%40BaBaCommmerce%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20that%20is%20%3CEM%3E%3CSTRONG%3Enot%3C%2FSTRONG%3E%3C%2FEM%3E%20a%20%22sample%20of%20%5Byour%5D%20worksheet%22--%20it's%20just%20an%20image.%20We'd%20be%20better%20able%20to%20hep%20if%20you%20attach%20an%20actual%20copy%20of%20the%20workbook%20or%20at%20the%20very%20least%20a%20couple%20of%20the%20worksheets.%20Not%20images.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%3A%20some%20questions%20for%20you%3C%2FP%3E%3COL%3E%3CLI%3Ewhat%20computer%20system%20are%20you%20on.%20I%20suspect%20that%20something%20like%20%3CSTRONG%3EPower%20Query%3C%2FSTRONG%3E%20may%20be%20the%20solution--and%20I%20can't%20help%20with%20that%2C%20because%20it's%20not%20on%20Macs%2C%20which%20is%20my%20operating%20system--but%20others%20could.%3C%2FLI%3E%3CLI%3EHow%20new%20is%20your%20Excel%3F%20The%20most%20current%20version%20has%20some%20powerful%20functions%20that%20could%20help%20as%20well.%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%2C%20as%20one%20example.%3C%2FLI%3E%3CLI%3EHow%20dynamic%20are%20the%20data%20arrays%20on%20your%20sheets%3F%20If%20they're%20fairly%20static%20(e.g.%2C%20changing%20only%20monthly)%2C%20I%20would%20solve%20it%20myself--in%20the%20absence%20of%20Power%20Query--by%20combining%20all%20those%20different%20sources%20into%20a%20single%20Database%2C%20which%20is%20a%20lot%20easier%20to%20search%20using%20criteria%20such%20as%20you%20seem%20to%20be%20seeking.%20Power%20Query%20will%20do%20that%20combining%20for%20you%2C%20but%20it%20you%20too%20are%20on%20an%20Apple%20computer%20you%20might%20want%20to%20consider%20creating%20a%20single%20database%20rather%20than%20having%20separate%20ones.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I would like you to help me with the formula that I am looking for I wanna a formula that will allow to me comparing date and value (like products, SKU products model ) with the 7 sheets and get me the matched value and lowest price and sheet name 

I have attached a sample of my worksheet 

 

image001.png

3 Replies
best response confirmed by BaBaCommmerce (New Contributor)
Solution

@BaBaCommmerce 

 

First, that is not a "sample of [your] worksheet"-- it's just an image. We'd be better able to hep if you attach an actual copy of the workbook or at the very least a couple of the worksheets. Not images.

 

Second: some questions for you

  1. what computer system are you on. I suspect that something like Power Query may be the solution--and I can't help with that, because it's not on Macs, which is my operating system--but others could.
  2. How new is your Excel? The most current version has some powerful functions that could help as well. FILTER, as one example.
  3. How dynamic are the data arrays on your sheets? If they're fairly static (e.g., changing only monthly), I would solve it myself--in the absence of Power Query--by combining all those different sources into a single Database, which is a lot easier to search using criteria such as you seem to be seeking. Power Query will do that combining for you, but it you too are on an Apple computer you might want to consider creating a single database rather than having separate ones.

 

 

@mathetes  thanks for your  reply please find the list and the I am working on windows 10 

@BaBaCommmerce 

 

You are a person of few words.

 

One of the first problems you're going to need to address is that of getting more consistency between the various lists. My quick review suggests that sheets 1, 2 and 7 provide the most helpful formats. Pick one of those and make it the "standard" and then reorganize the others--to the extent possible--so the columns contain comparable and consistent data.

 

As it is, I have a hard time figuring out how even Power Query could work--it needs some kind of consistent "key" or "identifier" on each table for each kind of device; right now you don't have that. (For example, look at how Lenovo devices are identified and described on your sheet 1 and your sheet 7. And that does no more than scratch the surface.)

 

You've heard, I presume, the old computer adage, "GIGO"?

 

"Garbage in, garbage out."

 

It's an unkind description, but it basically is saying that if your data is full of glaring inconsistencies, you're not going to be able to work effectively with it.