?
Solved

Excel to "Bucket" each row by rules

Posted on 2016-11-24
7
Medium Priority
?
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 34

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 33

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 33

Accepted Solution

by:
Rob Henson earned 1500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 33

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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