This might be a stupid question.....

RebeccaMulder1975

New Member
Joined
Apr 16, 2018
Messages
1
...but after days of dedicated Googling that have not yielded results, I need help.

In a nutshell, I use huge datasets to run a dashboard and to make it work I need to relabel each line of data to tell me what Division it applies to, and what Function. Bearing in mind these are workforce reports, they're huge (over 3000 rows with multiple columns for each).

Up until now, I have been happily using COUNTIF to tell me what the Division of an employee should be, based on their physical location. Example below - I use their GEO code to update the report with their associated division - I add a new column and embed this formula:

=IF($Z:$Z="AUT","Central Eastern Europe",<o:p></o:p>
IF($Z:$Z="AZE","HGEM",<o:p></o:p>
IF($Z:$Z="BEL","Western Europe",<o:p></o:p>
IF($Z:$Z="BGR","Central Eastern Europe",<o:p></o:p>
IF($Z:$Z="BIH","Central Eastern Europe",<o:p></o:p>
IF($Z:$Z="BLR","HGEM"))))))))))))))))))))))

This works fine, but now I need to apply the same logic to their Layer 1 Manager, which means a hell of a lot more variables to look at - got over 500 names I need to convert to the right job function, based on a list I have. My Countif won't work, probably because it's way too massive.

How do I do this? I would attach examples, but it's all super confidential and I want to stay employed. Thinking maybe I could use a macro to sort it out, but bloody clueless on those too.

Is there a more efficient way to run a Countif to cover such a huge dataset, and that will input the right function data in the new column I create, based on the Layer 1 Manager? Otherwise, it will have to be manual and that will take hours.

Help meeee!

Thanks

Rebecca

 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
i think the problem call for a lookup table of some sorts.

here is an example with lookup table in Cols A & B


Excel 2013/2016
ABYZAA
1AZEHGEMBELWestern Europe
2BELWestern EuropeBIHCentral Eastern Europe
3BGRCentral Eastern EuropeAZEHGEM
4BIHCentral Eastern Europe
Sheet1
Cell Formulas
RangeFormula
AA1=LOOKUP(Z1,$A$1:$A$4,$B$1:$B$4)
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top