Excel to "Bucket" each row by rules
Hi, I am trying to first determine the best way to identify the correct “bucket” that each file on the attached workbook should fall into – determined by the attached logic from the columns & dates.
The workbook I have is refreshable (Runs a query from power pivot) and then I need to run a macro to determine and add a column to the table which identifies the bucket the file should be in.
Not sure if a formula should be used (IFS) or should filter the columns within the macro.. and identify that way…. Or if there is a better way to do this.
Thanks
Rules.txt
Example.xlsx
The workbook I have is refreshable (Runs a query from power pivot) and then I need to run a macro to determine and add a column to the table which identifies the bucket the file should be in.
Not sure if a formula should be used (IFS) or should filter the columns within the macro.. and identify that way…. Or if there is a better way to do this.
Thanks
Rules.txt
Example.xlsx
Are the entries in each of the columns always dates or blank?
If so, you can use the following formula to create an ID for the line:
=COUNT([@[Media Received]])&COUNT([@[Lette
This will create an ID made up of 1s & 0s. You can then create a list of all possible combinations of 1s & 0s and apply a bucket title to each. The ID created on each line can then be used as a lookup on the table to get the relevant bucket title.
Thanks
Rob H
If so, you can use the following formula to create an ID for the line:
=COUNT([@[Media Received]])&COUNT([@[Lette
This will create an ID made up of 1s & 0s. You can then create a list of all possible combinations of 1s & 0s and apply a bucket title to each. The ID created on each line can then be used as a lookup on the table to get the relevant bucket title.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Norie, I just need to identify the correct bucket for each line in a new column. I am going to pivot everything else on a separate tab but know how to do that already. Was just trying to get a solution provided here for assigning the buckets
ASKER
Rob, I will take a look at this later and see if I cant get it to work
ASKER
Rob, I how are you proposing that we handle the rules where if one column has a date in it, then we need to make sure another column has one.. as well as the rule where if a date is in one column, we need to make sure that the date is more recent than that date.. as well as checking to make sure that the letter sent date is greater than 40 days ago... do you have a working solution for this as that would be simpler than me stumbling my way through it and is what im looking for..
For those cells where you need to do a specific check rather than just blank or not you can use a fairly simple logic check.
For example, if you wanted to check that the Letter Sent field was more than 40 days ago, rather than:
&COUNT([@[Letter Sent]])
use
&--(AND([@[Letter Sent]]<>"",[@[Letter Sent]]<TODAY()-40))
The -- at the beginning converts the TRUE or FALSE result to 1 or 0. The 1 or 0 result can then still be used as before.
Thanks
Rob H
For example, if you wanted to check that the Letter Sent field was more than 40 days ago, rather than:
&COUNT([@[Letter Sent]])
use
&--(AND([@[Letter Sent]]<>"",[@[Letter Sent]]<TODAY()-40))
The -- at the beginning converts the TRUE or FALSE result to 1 or 0. The 1 or 0 result can then still be used as before.
Thanks
Rob H
Are the rows for each bucket separated out into their own worksheet?