Solved

Excel to "Bucket" each row by rules

Posted on 2016-11-24
7
51 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
Comment Utility
What happens once each row is assigned a bucket?

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

Expert Comment

by:Rob Henson
Comment Utility
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 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Brent Guttmann
Comment Utility
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
Comment Utility
Rob, I will take a look at this later and see if I cant get it to work
0
 

Author Comment

by:Brent Guttmann
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

743 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

11 Experts available now in Live!

Get 1:1 Help Now