SOLVED
Home

Excel formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-477208%22%20slang%3D%22en-US%22%3EExcel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477208%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20problems%20on%20my%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20worksheet%201%20and%202.%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20worksheet%201%2C%20the%20yellow%20highlighted%20contains%20all%20the%20country%20name%20but%20i%20have%20to%20use%20worksheet%202%20as%20a%20data%20set%20(green%20highlighted%3B%20which%20contains%20country%20name%20with%20item%20'frequency')%2C%20and%20to%20find%20out%20the%20related%20frequency%20for%20those%20items%20in%20worksheet%201.%20I've%20tried%20Index%20and%20match%20functions%20but%20it%20can't%20help.%20Could%20anyone%20help%20on%20solving%20the%20problems%3F%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks!%3C%2FP%3E%3CP%3E(Attached%20a%20testing%20file%20for%20reference)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-477208%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-477264%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324295%22%20target%3D%22_blank%22%3E%40doraymi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20country%20name%20in%20F2%3A%3C%2FP%3E%3CPRE%3E%3DVLOOKUP(F2%2CSheet2!%24A%242%3A%24B%249%2C2%2CFALSCH)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477265%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324295%22%20target%3D%22_blank%22%3E%40doraymi%3C%2FA%3E%26nbsp%3B%2C%20could%20you%20please%20clarify%20-%20you%20have%206%20columns%20in%20Sheet1%20with%20country%20names%2C%20for%20which%20one%20you%20need%20the%20frequency%3F%20Or%20you'd%20like%20calculate%20the%20frequency%20based%20on%20these%20columns%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477347%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477347%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BBig%20thanks%20for%20your%20prompt%20reply!%20i've%20got%20the%20desired%20result.%20I%20would%20like%20to%20ask%20a%20more%20questions...after%20searching%20the%20frequency%20for%20each%20column%20(F%2C%20H%2C%20J%2C%20L%2C%20N%2C%20P%20and%20R)%20and%20gives%20out%20column%20(G%2C%20I%2C%20K%2C%20M%2C%20O%20and%20Q)%2C%20can%20I%20further%20sort%20out%20all%20the%20'High'%20frequency%20country%3F(say%20for%20row%202%2C%20sort%20out%20Thailand%2C%20Hong%20Kong%2C%20Indonesia%20and%20Thailand)%2C%20in%20order%20to%20achieve%20the%20goal%20in%20presenting%20the%20countries%20which%20have%20high%20frequency%20against%20the%20company%20name%20(column%20c)%20in%20pivot%20table.%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477354%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Byes%2C%20i'd%20like%20to%20calculate%20frequency%20for%20all%20six%20column%2C%20and%20then%20to%20list%20out%20all%20the%20most%20%22high%20frequency%22%20items%20(it%20might%20have%20more%20than%201%20high%20frequent%20country%20in%20each%20row)%2C%20so%20as%20to%20use%20those%20information%20and%20compare%20with%20company%20name%20(column%20C)%20in%20the%20pivot%20table.%20thanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477368%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477368%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324295%22%20target%3D%22_blank%22%3E%40doraymi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20provide%20a%20file%20with%20your%20desired%20result%3F%20What%20should%20the%20pivot%20table%20look%20like%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477407%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477407%22%20slang%3D%22en-US%22%3Ethis%20would%20be%20my%20desired%20result%2C%20in%20which%20list%20out%20the%20most%20high%20frequency%20(might%20not%20more%20than%20one%20country)%20against%20list%20of%20companies%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477426%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324295%22%20target%3D%22_blank%22%3E%40doraymi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHm%2C%20your%20pivot%20table%20shows%20only%20a%20grand%20total%20of%206.%20But%20it%20should%20be%2042%20(6%20rows%20by%207%20columns).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477449%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477449%22%20slang%3D%22en-US%22%3Eyes%2C%20because%20I%20just%20pick%20one%20column%20for%20trial%20in%20the%20last%20shot%20I%20sent%20you.%20%F0%9F%98%80%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477452%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477452%22%20slang%3D%22en-US%22%3Eyes%2C%20because%20I%20just%20pick%20one%20column%20for%20trial%20in%20the%20last%20shot%20I%20sent%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477490%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324295%22%20target%3D%22_blank%22%3E%40doraymi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20wasn't%20really%20your%20desired%20result.%3C%2FP%3E%3CP%3EMy%20solution%20requires%20Power%20Query.%3C%2FP%3E%3CP%3EThe%20result%20from%20PQ%20is%20loaded%20into%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478016%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478016%22%20slang%3D%22en-US%22%3Ebig%20thanks%20from%20your%20help%20again!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478075%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bsorry%20for%20bothering%20again%2C%20I've%20tried%20to%20replicate%20a%20pivot%20table%20as%20your%20file%20sent%20to%20me%20but%20fails%2C%20could%20you%20please%20help%20to%20advise%20on%20how%20to%20put%20the%20'Frequency'%20to%20the%20pivot%20table%3F%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478165%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324295%22%20target%3D%22_blank%22%3E%40doraymi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20stated%20before%20the%20data%20for%20the%20pivot%20table%20comes%20from%20Power%20Query.%3C%2FP%3E%3CP%3EOpen%20the%20PQ%20editor%20and%20see%20the%20queries%20on%20the%20left%20pane%20and%20the%20steps%20for%20each%20query%20on%20the%20right%20pane.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478470%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BI've%20learned%20how%20to%20get%20it!%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
doraymi
Occasional Contributor

Hi all,

 

I have a problems on my formula. 

I have two worksheet 1 and 2. 

for worksheet 1, the yellow highlighted contains all the country name but i have to use worksheet 2 as a data set (green highlighted; which contains country name with item 'frequency'), and to find out the related frequency for those items in worksheet 1. I've tried Index and match functions but it can't help. Could anyone help on solving the problems? 

thanks!

(Attached a testing file for reference)

14 Replies

@doraymi 

For the country name in F2:

=VLOOKUP(F2,Sheet2!$A$2:$B$9,2,FALSE)

@doraymi , could you please clarify - you have 6 columns in Sheet1 with country names, for which one you need the frequency? Or you'd like calculate the frequency based on these columns?

@Detlef Lewin Big thanks for your prompt reply! i've got the desired result. I would like to ask a more questions...after searching the frequency for each column (F, H, J, L, N, P and R) and gives out column (G, I, K, M, O and Q), can I further sort out all the 'High' frequency country?(say for row 2, sort out Thailand, Hong Kong, Indonesia and Thailand), in order to achieve the goal in presenting the countries which have high frequency against the company name (column c) in pivot table. thanks.

@Sergei Baklan yes, i'd like to calculate frequency for all six column, and then to list out all the most "high frequency" items (it might have more than 1 high frequent country in each row), so as to use those information and compare with company name (column C) in the pivot table. thanks! 

@doraymi 

Could you provide a file with your desired result? What should the pivot table look like?

 

this would be my desired result, in which list out the most high frequency (might not more than one country) against list of companies

@doraymi 

Hm, your pivot table shows only a grand total of 6. But it should be 42 (6 rows by 7 columns).

 

yes, because I just pick one column for trial in the last shot I sent you. 😀
yes, because I just pick one column for trial in the last shot I sent you.
Solution

@doraymi 

So it wasn't really your desired result.

My solution requires Power Query.

The result from PQ is loaded into the pivot table.

 

big thanks from your help again!

@Detlef Lewin sorry for bothering again, I've tried to replicate a pivot table as your file sent to me but fails, could you please help to advise on how to put the 'Frequency' to the pivot table? thanks.

@doraymi 

As I stated before the data for the pivot table comes from Power Query.

Open the PQ editor and see the queries on the left pane and the steps for each query on the right pane.

 

@Detlef Lewin I've learned how to get it! thanks!