Hi Chandoo,
I have a peculiar excel problem which i am not able to solve. I would need your help regarding the same. I have tried searching for multiple forumlas to arrive at a solution but failed to get an answer.
Below is the scenario -
1) I have one activity where i have to match Billings V/s Receipts for a month.
2) Attached example file has 2 tabs having 2 reports Billings & receipts which i need to match with each other.
3) I need to match "Rolls Billed" in Billings tab to "Rolls Billed" in Receipts tab. Both are totaling to same value ( Column I in both tabs)
4) The problem is that there is no commonality between the 2 data sets to match with each other.
5) Further more, the values "Rolls billed" in Billings tab are the Split/ breakout of "Rolls Billed" in Receipt tab. Which means some of the lines in Billings tab should tie up to one single line in Receipts tab.
6) We currently do this manually which is taking a lot of time to match. And this is just an example file. The real data runs in hundreds of lines each month and this is being done by 6 people every month for 35 facilities. So as u can see lot of time wastage and resource wastage is happening to match this manually.
7) We needed an excel formula or a statistical function in excel which would help us match which of the lines in 'Billings" tab total up to the lines in "Receipt" tab. You can see from the file that "Receipts" tab has only 6 lines (as good as subtotals of Billings lines).
8) Also in "Receipts" tab we have a hint in Column H - Net Rolls Billed - which is basically hinting at how many lines from first sheet will total upto the corresponding subtotal line in Receipts tab.
For example - in receipts tab "Cell H2" says 8 and "Cell I2" says 38509. Which means sum of any 8/50 lines in "Billing" tab should total upto 1st line in "Receipts" column. But to find this out we take so much time as we have to do so many permutation and combinations for matching and finding out Which 8 lines in 1st sheet would tie upto the 1st line in 2nd sheet.
Looking at the above issue, can you please help us to find out a possible shortcut to save time and resources. It can be a formula or a statistical function or a power query which can help us to simplify this matching process.
Please contact me if required a call - 9620691157
Shikhar M Vaswani
Hoping to receive a reply from you regarding the problem
I have a peculiar excel problem which i am not able to solve. I would need your help regarding the same. I have tried searching for multiple forumlas to arrive at a solution but failed to get an answer.
Below is the scenario -
1) I have one activity where i have to match Billings V/s Receipts for a month.
2) Attached example file has 2 tabs having 2 reports Billings & receipts which i need to match with each other.
3) I need to match "Rolls Billed" in Billings tab to "Rolls Billed" in Receipts tab. Both are totaling to same value ( Column I in both tabs)
4) The problem is that there is no commonality between the 2 data sets to match with each other.
5) Further more, the values "Rolls billed" in Billings tab are the Split/ breakout of "Rolls Billed" in Receipt tab. Which means some of the lines in Billings tab should tie up to one single line in Receipts tab.
6) We currently do this manually which is taking a lot of time to match. And this is just an example file. The real data runs in hundreds of lines each month and this is being done by 6 people every month for 35 facilities. So as u can see lot of time wastage and resource wastage is happening to match this manually.
7) We needed an excel formula or a statistical function in excel which would help us match which of the lines in 'Billings" tab total up to the lines in "Receipt" tab. You can see from the file that "Receipts" tab has only 6 lines (as good as subtotals of Billings lines).
8) Also in "Receipts" tab we have a hint in Column H - Net Rolls Billed - which is basically hinting at how many lines from first sheet will total upto the corresponding subtotal line in Receipts tab.
For example - in receipts tab "Cell H2" says 8 and "Cell I2" says 38509. Which means sum of any 8/50 lines in "Billing" tab should total upto 1st line in "Receipts" column. But to find this out we take so much time as we have to do so many permutation and combinations for matching and finding out Which 8 lines in 1st sheet would tie upto the 1st line in 2nd sheet.
Looking at the above issue, can you please help us to find out a possible shortcut to save time and resources. It can be a formula or a statistical function or a power query which can help us to simplify this matching process.
Please contact me if required a call - 9620691157
Shikhar M Vaswani
Hoping to receive a reply from you regarding the problem