How to show values matching each cell values

Hi All,

I have spreadsheet with the names of the cities and the names of the products purchased in each city.
I would like to do a count of the number of times each product was purchased in each city. Please can you tell the formulae used for this.

thank you
Kevin

Hi there!

I agree with judgeh, a pivot table would be a great solution.

However if you're not using it, I set up a small file to help you get started if you like:

Book1.xlsx

Hi,

Thanks for replying, I didnt know about Pivotal table but have started using that now. Thanks for this. I have another question related to this now.

So basically from the new pivot table, I now need to pull data of the products from the list countries which are at the moment in the from of country codes,

I created a list with column filled with the full list of country codes I need, and column B with list of the associated countries for those code. Since the raw data has countries codes associated to the product purchases. I would like to replace the country codes listed in the pivot table with the actual countries, any idea what formulae is used to replace this data accordingly.

kevin

hi,

due to the nature of the data, I cannot share this here, however, I saw this idea shared on some other forum which I am yet to test out, but I will let you know if this works:

1. Copy and paste (or enter) the following formula in cell C2 =TRIM(MID(B2,SEARCH("-",B2)+1,LEN(B2)-SEARCH("-",B2)))

2. Copy and paste (or enter) the following formula in cell D2 =--LEFT(B2,3)

3. Copy the formulas from steps 1 and 2 down the required number of rows

4. Copy and paste (or enter) the following formula in cell E2 =VLOOKUP(A2,$C$2:$D$5,2,FALSE)&"-"&VLOOKUP(A2,$C$2:$D$5,1,FALSE) but make an adjustment for the last row in Column D (i.e. change 5 to your last data row)

5. Copy the formula from step 4 down the required number of rows

6. Convert Column E to values and then cut (move) it over to Column A

7. Delete Columns C and D


although I dont know if this single line formulae achieves the same as the above 7 steps: =INDEX($B$2:$B$20000,MATCH("*"&A2,$B$2:$B$20000,0))

For example:
Column A
Atlanta
Concord
Nashville
Memphis

Column B
123- Concord
768-Memphis
123-Nashville
333-Atlanta

(column B should replace column A, then the steps above is expected to this) - any comments on this method?

Please post a sample of the data. Replace your country codes & product names, delete what has to be to preserve confidentiality but I think we need to visualize a bit better- At least I need to^^