• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing/Merging data labels prior to pivoting a column into headers.

M

MrTan4of5

New Member
Please forgive me in advance for the length of this post, but I’ve been trying to figure this out for over a month, and I need some help!
In the attached sample Excel file (Click here --> BL-16 Report Converter), you will find three tabs, “Original BL-16”, which is the original format that I received the file in, “Merged Data”, which is the database that resulted from my first go round in the power query program, and “Data Form”, which is a sample of what we would like to have as the final report.
Task No. 1: I need to change many of the item names in the CAPTIONS column in the “Merged Data” tab to match others, because there are numerous data items that have the same information, but the names are slightly different. (The BL-16 report is a compilation of billing data from various utility companies.) We want to narrow it down from over 190 variations to approximately 40 names.
Task No. 2: When I transpose the CAPTIONS column, the values that are linked to the headings, come from two different numeric columns. The first is the VALUE column, which has non-monetary values, and the other is the COST column, which has monetary values. The differentiation is indicated in a third column, UNIT column, where the indication is shown by either “dollar” or any other value. My question is, do I need to split the CAPTION column into two calculated columns, and then pivot each of those separately, using their own value column, or is there a way for it to determine which is which when the column heading is split?
Task No. 3: After all is said and done, I need to be able to create a pivot table, similar to that in the “Data Form” tab, using all the aforementioned columns, but only displaying the columns that have data in them when the desired client and 12-month period is selected (using slicers). THANK YOU!
 
I've done the sort of thing in #1 pretty often over the years. For some RBAC projects I send out scores or hundreds of worksheets for other departments or analysts to fill in; they come back with lots of variations on "yes", "YES", "never", "0", "ok" and so forth, and before I run them through my input program I have to standardize them. It's pretty easy: Just write a program that recogizes and converts the most common variations to the desired standard values. As you encounter new variations you can add them to your program. Or if you want to get fancy, you can have a worksheet listing all the variations you've seen so far, along with the proper conversion, and add to that worksheet whenever you see a new variety.

You may even be able to do this conversion using the VLOOKUP function, but assuming this isn't a one-time chore you'd have to do a little work every time you want to do another set of conversions. If it's just the one time, though, VLOOKUP may be your best option. It'd take three or fours steps, though. Let me know if you don't see how it would work.

I never use pivot tables, so items 2 and 3 I can't help with.
 
BobBridges said:
I've done the sort of thing in #1 pretty often over the years. For some RBAC projects I send out scores or hundreds of worksheets for other departments or analysts to fill in; they come back with lots of variations on "yes", "YES", "never", "0", "ok" and so forth, and before I run them through my input program I have to standardize them. It's pretty easy: Just write a program that recogizes and converts the most common variations to the desired standard values. As you encounter new variations you can add them to your program. Or if you want to get fancy, you can have a worksheet listing all the variations you've seen so far, along with the proper conversion, and add to that worksheet whenever you see a new variety.

You may even be able to do this conversion using the VLOOKUP function, but assuming this isn't a one-time chore you'd have to do a little work every time you want to do another set of conversions. If it's just the one time, though, VLOOKUP may be your best option. It'd take three or fours steps, though. Let me know if you don't see how it would work.

I never use pivot tables, so items 2 and 3 I can't help with.
Click to expand...

Hi Bob, thanks for responding, did you intend to attach a sample and I missed it? I'm not real sure of what type of program I need to write, do you mean a series of functions in a Excel spreadsheet? I'm a newby to that level of proficiency. :-( But I'm learning...you know the old adage: Old dog, New tricks. Anyway, thanks for trying.y
 
Tan4of5, no, I was just describing the process generally. If you're not sure how to implement it we'll have to discuss more. Is this a one-time thing you're doing, just this one collection of data, or are you going to have to do it again from time to time? The solution I'll recommend and walk you through depends on your answer to that question. If you have to do this multiple times (and in my experience once you demonstrate that you can solve a problem like this, people hand you more of them), you want to write a program that you can run again and again. If it's just one time, I'll probably suggest you just use VLOOKUP (and I'll describe how if you're not sure).
 
BobBridges said:
Tan4of5, no, I was just describing the process generally. If you're not sure how to implement it we'll have to discuss more. Is this a one-time thing you're doing, just this one collection of data, or are you going to have to do it again from time to time? The solution I'll recommend and walk you through depends on your answer to that question. If you have to do this multiple times (and in my experience once you demonstrate that you can solve a problem like this, people hand you more of them), you want to write a program that you can run again and again. If it's just one time, I'll probably suggest you just use VLOOKUP (and I'll describe how if you're not sure).
Click to expand...
BobBridges
Thanks! This is just the beginning, all this info will be updated monthly.
 
Last edited:
So it's definitely better to write a program that you can run once a month, without having to do the same setup over and over. And once you have a program (this according to my experience), you'll start improving it. Pretty soon it'll fetch the data, standardize those values, add formulae and formatting, and save itself to a new location—and email itself to your boss, too, if that's what you do. But one thing at a time.

So if we're going to write a program together, my first question is: How much do you already know about VBA programming? Are you starting from scratch, or do you already know the basics? And if not VBA, have you programmed in any language?
 
I'm Pretty good at writing formulas in Excel, but never really dealved into VBA. My first real job was promamming in Basiic (40+ years ago, so I understand the logic side of it. In Power Query, I can't get it to run a Table.ToColumn, List.Zip or Table.ReplaceValues without getting an ERROR! What jam I doing (or not doing) wrong?

Just an aside: I'm disabled and run everything (remotes to computers) with a stick in my mouth,, so needless to say, my neck is getting tired of 12 hour days trying to figure this out. BTW: I'm Ted.
 
You must log in or register to reply here.
Back
Top