Struggling to work around this problem I have in Excel at the moment, any help will be greatly appreciated!
Basically I have a list of company names and wish to categorize them using a formula. Theres about 20 different companies but only 5 sections I want to split them into. I could easily manually enter it but I'd like to be able to just Cntrl D down and make it work for anything I might add in the future.
What I'm thinking about so far is using a long =IF function. Assume the range of data is in column C, the sections are listed in A1:A5 and I want the new entry to be in column D. Basically I need to include multiple =IF functions into 1 formula.
Any help would be amazing!
Why don't you just put the category names in a column next to the company names and then sort both columns according to the category column? If you don't want to see that column just hide it.
I could enter them manually of course, but ideally I'd like it automated for new entries.
What is the advantage of categorizing them using a formula as opposed to a filter?
If you're looking for a complete answer based off the information you've given, its not possible.
Yes nested if's will work for sorting data, but I feel there is an easier way, can you give a sanitised data sample so i can telll if you are filtering by numerical data, binary or other data type. e.g.
A 10 B 20 C 30 D 40 E 50
sort all companies over 30.
Unfortunately it's not numerical, its text. Here's a sample:
Company Company 1 Company 2 Company 3 Company 4 Company 5 Company 6 ......
Sector Manufacturing Pharmaceutical Technology Financial
It's very hard to explain in text but basically I'm looking to do 2 things: - Create a reference list which lists all the companies within each sector - Use a formula to check which sector the company in a given cell is in, and then return the text value of that sector
Again,
I really think this can be done using a filter and conditional formatting.
^Ya, it can. OP, you're overcomplicating this quite a bit.
Use MrExcel, which is the best Excel forum ever...I went from not knowing much to whipping up VBA scripts now.
This is significantly simpler, as mentioned above with filters.
If you have an undisclosed need to use formulae then it can be done, but it wont be very adaptable if a new industry comes in.
Got it working, definitely was trying to overcomplicate it haha. Thanks all!
What did you use?
$20 says filter
I know, I just want to hear him say it :D
I used lookup functions and 3 =IF commands within a formula in combination with a pivot table and a couple of VBA macros.
No, I used a filter :(
You're welcome ;D
Soluta voluptas accusantium magni voluptatem possimus. Velit qui qui quia. Aut nam ea voluptates beatae dolorem ea. Et unde cumque praesentium quibusdam. Velit voluptas qui ducimus rerum autem cupiditate. Porro neque in soluta sunt. Aut eaque reprehenderit qui officia.
Odit velit ipsam nihil minima quam consequatur. Atque facilis ut ut sequi occaecati laudantium rerum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...