Solved

Excel to "Bucket" each row by rules

Posted on 2016-11-24
7
53 Views
Last Modified: 2016-11-28
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
0
Comment
Question by:Brent Guttmann
  • 3
  • 3
7 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 41901244
What happens once each row is assigned a bucket?

Are the rows for each bucket separated out into their own worksheet?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41901550
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([@[Letter Generated]])&COUNT([@[Letter Reviewed]])&COUNT([@[Letter Approved]])&COUNT([@[Letter Rejected]])&COUNT([@[Letter Sent]])&COUNT([@[Letter Returned]])&COUNT([@[Affidavit First Request]])&COUNT([@[Affidavit Requested]])&COUNT([@[Affidavit Received]])&COUNT([@[Validation Request Received]])&COUNT([@[Validation Requested]])&COUNT([@[Validation Letter Generated]])&COUNT([@[Validation Reviewed]])&COUNT([@[Validation Rejected]])&COUNT([@[Validation Approved]])&COUNT([@[Validation Sent]])&COUNT([@[First Action Generated Date]])&COUNT([@[First Action Reviewed]])&COUNT([@[First Action Approved]])

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
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41901655
See attached.

I have copied your rule text into a separate sheet and starting figuring out which columns will be 1 or 0 based on the rules.

Once you have the rest of these figured out you should be bale to match the series of 1s and 0s to the list of Bucket titles.

Thanks
Rob H
Example--2-.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Brent Guttmann
ID: 41901665
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
0
 

Author Comment

by:Brent Guttmann
ID: 41901674
Rob, I will take a look at this later and see if I cant get it to work
0
 

Author Comment

by:Brent Guttmann
ID: 41902345
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..
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41903790
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now