Apr 29 2022 08:02 AM
Hi, is there any formula in excel or technique to place bills existing in another sheet to place under receipt and remaining bills under the 2nd receipt and so on.
Apr 30 2022 11:23 AM
May 08 2022 09:17 PM
@mtarler thanks for the response.
Sheet A contains list of receipts from customer and sheet B contains number of bills invoiced to customer. Problem is that there is no common identifier in both sheets. I want to place the number of bills from sheet B against receipts in sheet A upto the receipt amount. Sample are attached below;
May 12 2022 04:39 AM
@Rizwan_Hussain I still don't think we have enough information. I took a stab at it and made an assumption that the bills were in order of the receipts and used the cumulative totals but I don't think that is the case. Here is the attachment but i think we need more information on HOW you expect the items to get matched up.
May 12 2022 10:00 PM
@mtarler Below sample is attached performed on one receipt. Bill amount from billing sheet upto receipt amount 2,237,003 is extracted and placed under receipt amount manually. I want to develop a formula that helps me in extracting billing amount and place under receipt amount.
May 13 2022 07:23 PM
@Rizwan_Hussain I'm still unclear what you need. In the example I sent I think I did what you want. I am attaching your sheet with the previous formulas (modified a little) and color coded a bit. So on the Receipt sheet I added a cumulative column and on the Billing sheet I added Receipt# sheet which creates that "tie back" to the receipt sheet you wanted. If you need to combine the 2 tables together that is another step (or better yet would be if you could use a pivot table depending on what you need).
I formatted both as tables as that should improve the overall performance over referencing arbitrarily long columns unnecessarily.
The formulas just take the receipts sequentially as you noted there is not other 'tie-back' to use and even the dates are not in order.
May 13 2022 09:30 PM
@mtarler Thanks for the response,
But there is a little problem with this approach if receipts number column is showing the description in cash / Visa instead of receipts number as appearing in previously shared sample. Further Attached sample is explaining completely my requirements in "desired sheet". Computation performed manually.
May 14 2022 06:59 PM
@Rizwan_Hussain I don't see any example of 'cash / Visa' in any of the files. If those codes mean that to you, fine but I don't have a clue. I used the column under Receipts called Receipt Number because it looked like a unique ID but if that isn't the case then just add another column that is a unique ID (e.g. =ROW() would work)
As for your "desired sheet" it is both a pain to do and not particularly useful for any further calculations or analysis. I suspect this is purely for printing a report for someone who is used to seeing it this way. In the attached I added this 'unique id' column as noted, joined the tables and created a pivot table using the combined data into a format at least very close to your 'desired output'.
I really wish I could better understand what you are actually trying to do/accomplish because I really think you could have a better solution.
May 15 2022 11:07 PM
@mtarler Thank you so much,
Facing issue in applying the "=_xlfn.XLOOKUP(SUM(Table2[[#Headers],[Amount]]:[@Amount])-[@Amount]+0.1,receipts[cumulative],receipts[unique ID],,1)" formula in billing sheet as attached below.
Can you please guide.
May 16 2022 02:36 AM
@mtarler, when sheet is opened Xlookup formula in billing sheet turned into #name error. =_xlfn.XLOOKUP(SUM(Table2[[#Headers],[Amount]]:[@Amount])-[@Amount]+0.1,receipts[cumulative],receipts[unique ID],,1)
How can I solve this?
Thanks for the support.
May 16 2022 06:18 AM
Solution@Rizwan_Hussain Ah, you must have an older version of Excel that doesn't recognize XLOOKUP. I have changed it to use INDEX( MATCH () ) combo in the attached.
May 19 2022 08:04 AM
May 16 2022 06:18 AM
Solution@Rizwan_Hussain Ah, you must have an older version of Excel that doesn't recognize XLOOKUP. I have changed it to use INDEX( MATCH () ) combo in the attached.