How to categorize companies in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2979532%22%20slang%3D%22en-US%22%3EHow%20to%20categorize%20companies%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979532%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3EFirst%20off%3B%20thank%20you%20for%20taking%20the%20time%20to%20help%20me.%3C%2FP%3E%3CP%3EI've%20been%20looking%20for%20the%20right%20fix%20for%20my%20problem%20but%20can't%20wrap%20my%20head%20around%20it.%3C%2FP%3E%3CP%3ESolution%3A%20formula%20which%20categorizes%20companies%2C%20in%20category%20A%2C%20B%20or%20both%2C%20based%20on%20money%20spent.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESituation%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI've%20got%20three%20excel%20files.%20Excel%20file%201%20contains%20companies%20of%20which%20i%20want%20to%20know%20whether%20they%20belong%20to%20Category%20A%2C%20Category%20B%20or%20both.%20The%20only%20way%20for%20me%20to%20know%20whether%20the%20company%20named%20in%20excel%201%20belongs%20to%20one%20of%20the%20categories%20is%20to%20look%20up%20the%20name%20or%20number%20in%20Excel%202%20%26amp%3B%203%20and%20check%20whether%20money%20was%20spent%20on%20them.%20If%20excel%202%20states%20a%20positive%20amount%20then%20the%20company%20belongs%20to%20category%20A%2C%20if%20excel%203%20states%20a%20positive%20amount%20then%20the%20company%20belongs%20to%20category%20B.%20However%2C%20if%20excel%202%20%26amp%3B%203%20state%20a%20positive%20amount%20then%20the%20company%20belongs%20to%20category%20A%20%26amp%3B%20B.%3CBR%20%2F%3E%3CU%3E%3CEM%3E(please%20read%20excel%20files%20containing%20data)%3C%2FEM%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAlready%20tried%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EI've%20used%20the%20IF%20function%20and%20Vlookup%20function%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CEM%3EVlookup%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E1.%20Look%20up%20the%20company%20name%20of%20excel%201%20in%20excel%202%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E2.%20If%20found%20name%20the%20amount%20spent%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIF%20function%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E1.%20If%20the%20amount%20is%20%26gt%3B%3D0.01%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E2.%20If%20true%20then%20return%20%22category%20A%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E3.%20If%20False%20then%20return%20%22no%20spend%20or%20Negative%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20done%20the%20same%20for%20category%20B%20but%20repalced%20excel%202%20for%20excel%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EProblems%20encountered%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20have%20encountered%20the%20following%20problems%3C%2FP%3E%3CUL%3E%3CLI%3EBecause%20of%20wrongly%20spelled%20names%20or%20case%20sensitivity%20some%20suppliers%20are%20not%20found%20and%20excel%20returns%20%23N%2FA%3C%2FLI%3E%3CLI%3EExcel%202%20and%203%20might%20contains%20multiple%20dates%20on%20which%20money%20has%20been%20spent.%20With%20Vlookup%20the%20first%20money%20spent%20is%20found%20and%20returned.%20So%20if%20the%20first%20money%20spent%20is%20-20%2C%20excel%20will%20return%20%22No%20Spend%20or%20Negative%22.%20However%2C%20the%20other%20dates%20have%20spent%20which%20are%20positive%20(e.g.%20%2B40%20or%20%2B50).%20Because%20Vlookup%20directly%20takes%20the%20first%20found%20value%20and%20doesn't%20look%20further%2C%20a%20company%20can%20be%20categorised%20incorrectly.%3C%2FLI%3E%3CLI%3EIf%20the%20company%20is%20not%20found%20in%20excel%202%20or%203%2C%20the%20function%20returns%20%23N%2FA.%3C%2FLI%3E%3CLI%3Esome%20companies%20have%20nagetive%20money%20spent%20but%20in%20total%20it%20is%20positive.%20For%20example%3B%20first%20time%20money%20spent%20on%20a%20company%20is%20-20%2C%20the%20second%20time%20it%20is%2040%20and%20the%20third%20time%20it%20is%2010.%20The%20total%20is%20still%20positive%20because%20-20%20%2B%2040%20%2B10%20%3D%2030%20spent.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EActions%20taken%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20following%20solutions.%3C%2FP%3E%3CUL%3E%3CLI%3ETo%20prevent%20wrongly%20spelled%20names%2C%20I%20have%20replaced%20the%20name%20with%20supplier%20number.%20Shortly%20said%3B%20supplier%20number%20will%20be%20looked%20up%20in%20excel%202%20and%203%20instead%20of%20name.%20However%2C%20i%20still%20receive%20the%20same%20output.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExcel%20Files%20and%20the%20containing%20data%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EExcel%201%20contains%3C%2FP%3E%3COL%3E%3CLI%3EApproved%20companies%3C%2FLI%3E%3CLI%3ECompany%20name%3C%2FLI%3E%3CLI%3ECompany%20number%3C%2FLI%3E%3CLI%3EBunch%20of%20other%20irrelevant%20data%3C%2FLI%3E%3C%2FOL%3E%3CP%3EExcel%202%20contains%3C%2FP%3E%3COL%3E%3CLI%3EMoney%20spent%20on%20companies%20%3CU%3E%3CEM%3Ewhich%20belong%20to%20category%20A%3C%2FEM%3E%3C%2FU%3E%3C%2FLI%3E%3CLI%3ECompany%20name%3C%2FLI%3E%3CLI%3ECompany%20number%26nbsp%3B%3C%2FLI%3E%3CLI%3EAmount%20of%20money%20spent%3C%2FLI%3E%3CLI%3ECurrency%3C%2FLI%3E%3CLI%3Ebunch%20of%20other%20irrelevant%20data%3C%2FLI%3E%3C%2FOL%3E%3CP%3EExcel%203%20contains%3C%2FP%3E%3COL%3E%3CLI%3EMoney%20spent%20on%20companies%3CU%3E%3CEM%3E%20which%20belong%20to%20category%20B%3C%2FEM%3E%3C%2FU%3E%3C%2FLI%3E%3CLI%3ECompany%20name%3C%2FLI%3E%3CLI%3ECompany%20number%26nbsp%3B%3C%2FLI%3E%3CLI%3EAmount%20of%20money%20spent%3C%2FLI%3E%3CLI%3ECurrency%3C%2FLI%3E%3CLI%3Ebunch%20of%20other%20irrelevant%20data%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20thank%20you%20for%20helping%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2979532%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2979865%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20categorize%20companies%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1222473%22%20target%3D%22_blank%22%3E%40Shahzad-akhtar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20more%20job%20for%20Power%20Query.%20If%20with%20formulas%20when%20use%20SUMIFS()%2C%20not%20lookup%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2979870%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20categorize%20companies%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979870%22%20slang%3D%22en-US%22%3EI%20did%20not%20understand%2Fcomprehend%20what%20you%20just%20said.%20I%20dont%20use%20power%20query.%20I%20want%20to%20use%20excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2979955%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20categorize%20companies%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1222473%22%20target%3D%22_blank%22%3E%40Shahzad-akhtar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20Query%20is%20part%20of%20Excel%2C%20this%20functionality%20is%20under%20Get%20%26amp%3B%20Transform%20tab.%3C%2FP%3E%0A%3CP%3EAgain%2C%20you%20may%20use%20SUMIFS()%20formulas%20to%20return%20companies%20spending.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear All,

First off; thank you for taking the time to help me.

I've been looking for the right fix for my problem but can't wrap my head around it.

Solution: formula which categorizes companies, in category A, B or both, based on money spent.

 

Situation:

I've got three excel files. Excel file 1 contains companies of which i want to know whether they belong to Category A, Category B or both. The only way for me to know whether the company named in excel 1 belongs to one of the categories is to look up the name or number in Excel 2 & 3 and check whether money was spent on them. If excel 2 states a positive amount then the company belongs to category A, if excel 3 states a positive amount then the company belongs to category B. However, if excel 2 & 3 state a positive amount then the company belongs to category A & B.
(please read excel files containing data)

 

Already tried:

  • I've used the IF function and Vlookup function

Vlookup

1. Look up the company name of excel 1 in excel 2

2. If found name the amount spent 

 

IF function

1. If the amount is >=0.01

2. If true then return "category A"

3. If False then return "no spend or Negative"


I have done the same for category B but repalced excel 2 for excel 3.

 

Problems encountered:

I have encountered the following problems

  • Because of wrongly spelled names or case sensitivity some suppliers are not found and excel returns #N/A
  • Excel 2 and 3 might contains multiple dates on which money has been spent. With Vlookup the first money spent is found and returned. So if the first money spent is -20, excel will return "No Spend or Negative". However, the other dates have spent which are positive (e.g. +40 or +50). Because Vlookup directly takes the first found value and doesn't look further, a company can be categorised incorrectly.
  • If the company is not found in excel 2 or 3, the function returns #N/A.
  • some companies have nagetive money spent but in total it is positive. For example; first time money spent on a company is -20, the second time it is 40 and the third time it is 10. The total is still positive because -20 + 40 +10 = 30 spent.

Actions taken:

I have tried the following solutions.

  • To prevent wrongly spelled names, I have replaced the name with supplier number. Shortly said; supplier number will be looked up in excel 2 and 3 instead of name. However, i still receive the same output.

 

Excel Files and the containing data

Excel 1 contains

  1. Approved companies
  2. Company name
  3. Company number
  4. Bunch of other irrelevant data

Excel 2 contains

  1. Money spent on companies which belong to category A
  2. Company name
  3. Company number 
  4. Amount of money spent
  5. Currency
  6. bunch of other irrelevant data

Excel 3 contains

  1. Money spent on companies which belong to category B
  2. Company name
  3. Company number 
  4. Amount of money spent
  5. Currency
  6. bunch of other irrelevant data

 

Again thank you for helping me.

6 Replies

@Shahzad-akhtar 

That's more job for Power Query. If with formulas when use SUMIFS(), not lookup functions.

I did not understand/comprehend what you just said. I dont use power query. I want to use excel.

@Shahzad-akhtar 

Power Query is part of Excel, this functionality is under Get & Transform tab.

Again, you may use SUMIFS() formulas to return companies spending.

Hi @Shahzad-akhtar

Power Query as well as Visual Basic macro language are both parts of an Excel installation but they demand knowledge very different from the spreadsheet.

Using SUMIFS to find the total amount spent for each company mentioned in table Excel_1 will calculate the sum no matter their order respective tables.

If the amount is greater than zero, the IF statement should result in a letter, A or B.

The two are then concatenated using &. 

=IF(SUMIFS(Excel_2[4. Amount of money spent];Excel_2[3. Company number ];[@[Company number]])=0;"";"A")
&
IF(SUMIFS(Excel_3[4. Amount of money spent];Excel_3[3. Company number ];[@[Company number]])=0;"";"B")

 

Excel_1 columns E to I calculates this step by step while col J makes it all in one formula.

bosinander_0-1637350462321.png

 

If you are not using 'pyamas' tables, change the column references to your columns, eg cell J3 using ranges in other files like

=IF(SUMIFS([Book2]Sheet1!$D:$D;[Book2]Sheet1!$C:$C;C3)=0;"";"A")
&
IF(SUMIFS([Book3]Sheet1!$D:$D;[Book3]Sheet1!$C:$C;C3)=0;"";"B")

 

Oh thank you. I did not know that.
I will read your entire comment and try to apply it. Thank you for your time and of course the pictures you have provided. I appreciate the effort you took to replicate in excel that which i mentioned.