[BettyAtKitchen]

I know this can be done, but I just cannot think how.

Store A Store B Store C Store D
111 111 222 555
222 333 333 666
333 555 666 888
444 777 888 999

Stores A, B, C and D sell products 111,222,333,444,555,666,777,888,999

My list has 15 colums and 10,000 rows so I really need help on this.

how can i find out Store A sells that are also sold be Store C.
I guess this is like cross-referencing

[MRdNk]

Check out Data>Pivot Table.
If you Google Excel Pivot tables, you should get some good info on how to use it.
Alternatively a Macro could be written to give you this information.

If you can't work out Pivot Tables, can be hard to understand at first, then let post an example spreadsheet with dummy data, and we can sort something out, dummy data, and how you want the results to be laid out.

[BettyAtKitchen]

sorry. i tried to upload in csv, excel 2003 and 2007. is there any other way i can save an excel file so i can upload?

[BettyAtKitchen]

Thanks, MRdNk. I tried pivot table, but it would not work for me in this case. I agree with a macro, except I do not know how to do it.

[jimr381]

What exactly are you looking for as the end result? Are you looking for the part number, store A amount and store b amount?

[bomb #21]

Originally Posted by BettyAtKitchen

I tried pivot table, but it would not work for me in this case. I agree with a macro, except I do not know how to do it.

Pivot table won't work for that layout.

As for macro ... depends on how you actually want to use this. What springs to my mind is, a five column table. Col 1 = products (unique list), cols 2-5 = "Store A - D" populated with formulas to return "Yes" if stocked, "No" if not. Then you could use Autofilter to display products stocked at A and C, for example. See Sheet2 of attached.

HTH

[MRdNk]

That's a pretty good solution / starting point from bomb #21.

As jimr381 notes, it depends on how you want the results to be displayed, do you want to be able to search by Product, want to send out a list of where someone can buy a particular product, or which is the best store for 5 of the products on the least number of visits, or general statistics.

[BettyAtKitchen]

I uploaded a sample file.

The result I would like is to show the stores that sells bananas and plums, and spinach and beets.

I would like to be able to cross-reference the stores to the fruits and the stores.

thanks for all your responses.

[bomb #21]

You could do that with the layout you have with a couple of autofilters, theoretically; see attached screenshots.

In practice, you can't "show the stores that sells bananas and plums, and spinach and beets" -- because no store sells bananas and plums.

[slurpee55]

Betty, there are a few things that you might want to do with your file. For one thing, you have both "apples" and "apple" as fruits - something that will mess up any search or filtering. I think I would also use text to columns to split your listings up, so that you have 3 fruits sold and 2 vegetables (or however many are needed. That way you can filter across several.
Another problem with filtering the way bomb has laid out is that if a store sells bananas and pineapples - but not apples - it will see that it contains "apple" and show it anyway.

[bomb #21]

All your points taken. The first thing I tried before my last post was a text-to-columns restructure. But I think that's a non-starter, because you can't filter >1 fruit columns ... uhh, "progressively"?

Say you want to check stores that have bananas plus whatever veggie. A-010 has banana in column C. But if you filter by column B first, you filter out A-010 and can't then filter it back in by column C.

Does that make any sense?

[slurpee55]

well, if the foods were listed alphabetically you could avoid that, but they aren't.
How about using something like:
=IF(B2="apple","apple", IF(C2="apple","apple", IF(D2="apple","apple",""))) in a new column (basing this on the text to columns files still). Of course, you would have to do it for every fruit and vegetable, but ultimately you would have a column listing every store that sold each fruit or vegetable

[Nic Cunliffe]

Pivot works but your data must be in two columns and repeat the store name for each relevant product line.

StoreProductstore a111store a111store a222store a555Store b222Store b333Store b333Store b666store c333store c555store c666store c888store d444store d777store d888store d999

Putting product in col data and store in row data and count of product in data layout will work for you.



Count of ProductProductStore111222333444555666777888999Grand Totalstore a2114Store b1214store c11114store d11114Grand Total22312212116

Regards - Nic

[Nic Cunliffe]

Sorry, hate the way the reply looks great until you post the reply and then the format screws up.

Hope it still makes sense to you

[BettyAtKitchen]

Hi Slurpee and Bomb. I read, studied, and tried both your suggestions, and thank you for the time you spent on this.

I agree that text to column would not work. If i use filter to find which stores sells apples and also bananas, and if only store A sell bananas, and if store B sells apples only, then filtering would "filter out" each store's column.

is there like a mini-program or is it called a macro that can be created that would give the information with a few keystrokes? the end-users of this excel file are not very techy and creating a different formula for each item to be looked up would be quite a considered a monumental

thanks to all.