Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel to "Bucket" each row by rules

Posted on 2016-11-24
7
Medium Priority
?
65 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 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

610 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