help With Pivot tables

[lk26]

Hi,

I have a Spreadsheet and within it there is a column with dates and some cells are blank. when i make a pivot table with this spreadsheet, the blank cells show up with "(blank)" in them. Can I tell it not to put the "(blank)" in the cells and just actually be a blank cell?

Sincerely,

Lauretta​

 

[lk26]

OK, so if I'm understanding correctly:
1. You have 2 separate files
2. One is a sales order file that does not contain quantity and pricing
3. The other is perhaps a more detailed sales order file that contains qty & price but not some other information that's on the 1st file.
4. You're ultimate objective is to link the 2 files so that all information related to a particular item/sales order is on 1 row.

If this is the situation, a pivot table will not properly join 2 separate files. If you add the field "Pickup/Exchange Detail Lines Item Name" to your pivot table next to "Item", notice how they are different - so it's like matching apples with oranges. Some possible approaches that could work include:
1. Formulas (eg: vlookup()) to bring the few fields the 1st file is missing in from the 2nd file
2. MSQuery (or a database program) to approrpiately combine the 2 files into a single file by properly joining the appropriate fields.

Before I suggest anything specific, would you confirm if my interpretation is correct. If not correct, please let me know what I'm misinterpreting. If correct, please provide some more explanation on the 2 separate data sets - eg: are they imported from another system; is there a one-to-one row relationship or can one file contain more rows for a single record than the other file; if you were to manually match up a transaction between the 2 files, how would you do that.

Yes, I believe you are correct in your thinking. We have a separate system with ALL of the orders (deliveries and Pickups) and it keeps them separate until the end of the month when its time to send invoices. I have to pull the info out separately and I am figuring out how to combine them together so that I can then calculate how much each patient (and facility) will be charged for the month. as for the one to one relationship, I think it's one to one. if i was to manually do this, i would match sales order numbers from both 1st and then by equipment. the sales order from both will match patients....

for instance
Facility 1 patient 1 recieved a wheelchair and a bed on 1/25/12, then on 3/5/12 we picked up the wheelchair (i'll attach an example)
Questions 2.xlsx - 0.01MB

let me know if this is clear enough

 

[lk26]

I'm trying to get a projected amount for the month before the actual invoice drops

 

[RonB1111]

Consolidating data into 1 table:

1. Is it possible the "separate system" already has an existing report which consolidates all the information into 1 table? If yes, that would be the easiest approach and then just download the report to excel for the billing projections.

2. To add the quantity and price by formula to each row of the 1st table, the formula to use is vlookup and here is a link explaining this function:
http://www.contextures.com/xlfunctions02.html
In your situation, I would suggest having your datatable with the qty/prices on a separate worksheet beginning in column B (I'll call it Sheet2). Because you'll need to match on multiple fields, I'd use col A as a helper column containing the S/O#/Facil#/Patient/Item, so put this formula in cell A2 and copy down:
=B2&C2&E2&F2
Then on your 1st worksheet which contains Col A:H per your example, enter these formulas and copy each down:
For Qty in, say I2: =VLOOKUP(H2&F2&E2&G2,Sheet2!A:I,8,FALSE)
For Price in say J2: =VLOOKUP(H2&F2&E2&G2,Sheet2!A:I,9,FALSE)

For Projecting Billing:
Assuming this report does not already exist in the separate billing system, you'd probably use =IF() statements, but before giving specific formulas I have a few questions:
1. Is your extract from the separate order system done for the current month only, or do you extract all transactions from the system (completed in prior months as well as uncompleted transactions)?
2. Would you prefer the extract from the separate system to be current month only or all?
3. Using your 1st 2 examples, when a row does not have a pickup date in Feb, would you bill this time only to the end of Feb, or would you delay billing until early March when it does show a pick up date of 3/5 and bill thru 3/5. The same question for the end of Jan, since the equip was installed 1/25, would the end of Jan have been billed the prior month or would you bill it now. Finally - how would you know what partial billings may have previously been done.

 

[lk26]

I'm going to try the first one and see what I get. but I have looked and asked all around to find if I can pull it directly from the separate system with no luck. unfortunately, I have to answer your questionswhen i have more time, either later today or tomorrow. I truly appreciate your time with me on this!

 

[lk26]

Consolidating data into 1 table:

1. Is it possible the "separate system" already has an existing report which consolidates all the information into 1 table? If yes, that would be the easiest approach and then just download the report to excel for the billing projections.

2. To add the quantity and price by formula to each row of the 1st table, the formula to use is vlookup and here is a link explaining this function:
http://www.contextures.com/xlfunctions02.html
In your situation, I would suggest having your datatable with the qty/prices on a separate worksheet beginning in column B (I'll call it Sheet2). Because you'll need to match on multiple fields, I'd use col A as a helper column containing the S/O#/Facil#/Patient/Item, so put this formula in cell A2 and copy down:
=B2&C2&E2&F2
Then on your 1st worksheet which contains Col A:H per your example, enter these formulas and copy each down:
For Qty in, say I2: =VLOOKUP(H2&F2&E2&G2,Sheet2!A:I,8,FALSE)
For Price in say J2: =VLOOKUP(H2&F2&E2&G2,Sheet2!A:I,9,FALSE)

For Projecting Billing:
Assuming this report does not already exist in the separate billing system, you'd probably use =IF() statements, but before giving specific formulas I have a few questions:
1. Is your extract from the separate order system done for the current month only, or do you extract all transactions from the system (completed in prior months as well as uncompleted transactions)?
2. Would you prefer the extract from the separate system to be current month only or all?
3. Using your 1st 2 examples, when a row does not have a pickup date in Feb, would you bill this time only to the end of Feb, or would you delay billing until early March when it does show a pick up date of 3/5 and bill thru 3/5. The same question for the end of Jan, since the equip was installed 1/25, would the end of Jan have been billed the prior month or would you bill it now. Finally - how would you know what partial billings may have previously been done.

yes. this is for current month only. I know i pulled info from past months but my concern once everything is said and done will be only for current month. we bill month to month. the new month will start new billing for anything not picked up yet. so, for feb billing will go to the end of feb and start again in march and will bill for the entire month unless picked up IN march. jan was the same-bill to the end of jan and start again in feb.

 

[RonB1111]

Can you confirm you've consolidated the data to 1 sheet and the formulas suggested for I2 and J2 are actually in those columns.

I'm still confused on how you determine if something was billed in the prior month. Using your example of equip delivered on 1/25 and still out with the patient, if you're billing the month of Feb 1-29, how do you know whether 1/25-1/31 was billed with the Jan billings. Also if equip delivered on 1/25 and picked up on 3/5, would you bill 3/1-3/5 with the Feb billings, or only bill 2/1-2/29 with Feb and wait until March to bill the final 5 days?

 

[lk26]

Can you confirm you've consolidated the data to 1 sheet and the formulas suggested for I2 and J2 are actually in those columns.

I'm still confused on how you determine if something was billed in the prior month. Using your example of equip delivered on 1/25 and still out with the patient, if you're billing the month of Feb 1-29, how do you know whether 1/25-1/31 was billed with the Jan billings. Also if equip delivered on 1/25 and picked up on 3/5, would you bill 3/1-3/5 with the Feb billings, or only bill 2/1-2/29 with Feb and wait until March to bill the final 5 days?

ok, maybe I am not explaining it correctly. I have attached a mock setup of the spreadsheet we currently used and the combine sheet i sent earlier. I need to pull the info from the new system and , in a way, make it like the old. my main goal is the totals. I need a template of this new worksheet that is easily updated weekly. so that when dates, items, charges, etc, are changed, the spreadsheet will also change. what I have attached won't have the same info between the two, but eventually, I will compare the new to the old to make sure the new is on point.
New Spreadsheet combined data.xlsx
Current Excel Mock Billing-Mar 12.xls
I hope this helps

 

[RonB1111]

I've looked at your spreadsheets. Let me know if my new understanding is correct before making suggestions.

You have an existing (old) workbook for billings that appears to involve substantial work to copy/paste each patient's data obtained from a non-excel based sales order system. You're trying to develop a new excel based billing workbook that minimizes or eliminates this tedious copy/paste task. It also should automatically recognize items whose final billing occured in prior months and automatically omit them from the current month's billing. It should also automatically calculate the number of days to bill this month for items not yet returned. You have an existing excel pricing table for each rental item with rates for daily/weekly/monthly and each item has a unique part # (either Item# or HCPC number).

The sales order system is not capable of doing the billings, so monthly billing invoices need to be calculated and generated from excel.

The only way you can get the data from the separate sales order system is 2 reports where the data can be copied into an excel sheet, but neither report has all the information to do the billing. I'm not sure how these 2 reports get into excel - eg: importing a text or csv file perhaps, please explain. These 2 reports are in your original uploaded file in the tab named "BT Combine Sheet" and the 1st report is columns A:H and the 2nd is in columns I:P and the rows do not line up between the 2 and there are more rows in the 2nd report than the first. Also, I don't see either Item# or HCPC number on either report, so I wondering how you match the descriptions to an Item/HCPC number.

Please let me know if the above is a reasonable interpretation of your situation and if anything relevant is missing from the above description.

Also, what program is the sales order system written - ie: MSAccess, SQL, some other database program, a purely custom/home grown program, etc. I'd like to find out if there is any possibility to directly link excel to this sales order system via an External Data Connection which you'll see in excel's data ribbon on the left side.

 

[lk26]

I've looked at your spreadsheets. Let me know if my new understanding is correct before making suggestions.

You have an existing (old) workbook for billings that appears to involve substantial work to copy/paste each patient's data obtained from a non-excel based sales order system. You're trying to develop a new excel based billing workbook that minimizes or eliminates this tedious copy/paste task. It also should automatically recognize items whose final billing occured in prior months and automatically omit them from the current month's billing. It should also automatically calculate the number of days to bill this month for items not yet returned. You have an existing excel pricing table for each rental item with rates for daily/weekly/monthly and each item has a unique part # (either Item# or HCPC number).

The sales order system is not capable of doing the billings, so monthly billing invoices need to be calculated and generated from excel.

The only way you can get the data from the separate sales order system is 2 reports where the data can be copied into an excel sheet, but neither report has all the information to do the billing. I'm not sure how these 2 reports get into excel - eg: importing a text or csv file perhaps, please explain. These 2 reports are in your original uploaded file in the tab named "BT Combine Sheet" and the 1st report is columns A:H and the 2nd is in columns I:P and the rows do not line up between the 2 and there are more rows in the 2nd report than the first. Also, I don't see either Item# or HCPC number on either report, so I wondering how you match the descriptions to an Item/HCPC number.

Please let me know if the above is a reasonable interpretation of your situation and if anything relevant is missing from the above description.

Also, what program is the sales order system written - ie: MSAccess, SQL, some other database program, a purely custom/home grown program, etc. I'd like to find out if there is any possibility to directly link excel to this sales order system via an External Data Connection which you'll see in excel's data ribbon on the left side.

Yes on your understanding of the old excel system

I am currently moving the billing to an online system. I am not sure what system the new one is written in. Also because we are switching over, there will be more pickup than deliveries (sales orders). The probem with the new system is that it cannot give me an estimated look at the invoice. Give me an estimate of how much a facility will pay at the end of the month (which the old system can do now). Which causes me to have to pull it out and piece it together somehow so tat I can get that estimate.

the new system is is downloaded as a csv file. i'll have to look into the external data connection. see if that is an option.

I didn't think I needed to pull the HCPC # since I had a Sales order # and an Item for that sales order, but I guess that makes sense for the Item itself...I can have to system pull it if that will help?

 

[RonB1111]

If you're moving the billing to a new online system and that system has the ability to generate csv reports, I would suspect that system has the ability for you to generate a custom report/query to pull all relevant fields into a single report that excludes equipment which has already been "final billed". If you aren't allowed to create a custom report, perhaps the online company could create one for you for a nominal fee, or better yet create the projected billings report you're looking for.

If you're stuck with the 2 reports method, let me know if the vlookup formulas I gave earlier are working properly - once you have it in a single report we can work on the projection formulas.