+ Reply to Thread
Results 1 to 18 of 18

Auto fill consecutive row after processing from different sheet.

  1. #1
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Auto fill consecutive row after processing from different sheet.

    Hello excel experts,

    I have problems with H2 from "Sell" sheet. I'm trying to get auto result in H2. IF the Coupon code in "Sell" sheet (C2:C32) have a match from any of (B2:B32) in "Buy" sheet, THEN it will calculate the value in "Sell" sheet G2, minus I2 in "Buy" sheet.
    The problem is, I need to get the value based on the coupon code, and not depending from the same row on "Buy" sheet.

    In "Cash balance" sheet, If there are any input in A2 from "Buy" sheet, it will input the same value (date) in A2. Also auto fill the C2 in "Cash balance"sheet with value from K2 in "Buy" sheet (the same row).
    As for the B2 in "Cash balance" sheet, it will be looking up from I2 in "Sell" sheet, also fill out the A2 in "Cash balance" sheet from the same row in any of Value from A2 in the "Sell" sheet.
    And the result will be in a consecutive row, based on the date.

    I may be asking too much, but it's a great challenge if It was using only the excel formulas. I was googling for it and I know I may need to use the VBA or macros which is lacks of my ability.
    So.. please help

    Regards,
    Peter
    Attached Files Attached Files
    Last edited by ganekon; 01-10-2011 at 10:41 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Auto fill consecutive row after processing from different sheet.

    I think this can be done just with formulas but your explanation is very difficult to understand, and the formulas you are using in your workbook do not make it any easier.

    I have compared your first paragraph to your workbook. The first problem is that the same coupon code appears multiple times in "Buy". So how do you determine which row applies to the "Sell" row?

    Second, it is completely unclear what your business rules are. If you could describe what you are actually trying to accomplish without diving down into Excel formulas it might be easier. For example, it is completely unclear why a calculation of weight would have anything to do with a coupon. In your situation what does "coupon" mean? If you hired me as a consultant I would say throw away your Excel workbook and tell me in detail what your requirements are so we can build the appropriate tool.

    I can't figure out the next paragraph at all.

    As a side note it is unnecessary to use

    =SUM(G2-H2)

    as seen in Buy!I2. Instead, use

    =G2-H2
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Quote Originally Posted by 6StringJazzer View Post
    I think this can be done just with formulas but your explanation is very difficult to understand, and the formulas you are using in your workbook do not make it any easier.

    I have compared your first paragraph to your workbook. The first problem is that the same coupon code appears multiple times in "Buy". So how do you determine which row applies to the "Sell" row?

    Second, it is completely unclear what your business rules are. If you could describe what you are actually trying to accomplish without diving down into Excel formulas it might be easier. For example, it is completely unclear why a calculation of weight would have anything to do with a coupon. In your situation what does "coupon" mean? If you hired me as a consultant I would say throw away your Excel workbook and tell me in detail what your requirements are so we can build the appropriate tool.

    I can't figure out the next paragraph at all.

    As a side note it is unnecessary to use

    =SUM(G2-H2)

    as seen in Buy!I2. Instead, use

    =G2-H2
    Hi 6StringJazzer.
    OK, let's make this clear. The Coupon is the number of the bill. So if we want to sell any product, the Coupon in "Sell" sheet will be looking for the coupon number in "Buy". If it has any match, then it will proceed. The problem is, some buyer won't always buy all kind of the products from the same bill number. Some times they just buy just triangle or just bowl.

    The row to apply in the "Sell" row could be any row from the "Buy". That's why there are same coupon's number in "Buy" but different "type" of product.

    [/QUOTE] For example, it is completely unclear why a calculation of weight would have anything to do with a coupon.[/QUOTE]
    You got the points there
    I need to link up the "Buy" and the "Sell" sheets, but to do that, I can only think the number of the bill on the paper. Do you have any suggestion for a better reference?

    Thanks for the input about the SUM, i was used to it, but i will change it.

    The next paragraph is about the "Cash Balance" Sheet. where I need to fill the Input if I got any total from each row in the "Sell".
    And Output in a different row if i got any total from the "Buy". So basically, an auto simple accounting from any purchase or sells we made in "Buy" and "Sell"

    Thanks for your effort in understanding my problem, I really appreciate that

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Auto fill consecutive row after processing from different sheet.

    Quote Originally Posted by ganekon View Post
    The Coupon is the number of the bill.
    I see. In the U.S. "coupon" means "an offer of a discount."

    So if we want to sell any product, the Coupon in "Sell" sheet will be looking for the coupon number in "Buy". If it has any match, then it will proceed.
    And here I'm still confused.

    First, it would help if you describe what you are trying to calculate in column H. To do that I expect that you would also have to describe what Weight, Box, and Net, and Decrease/Increase mean.

    It seems to me that you can't just match on any coupon number in Buy because the Net is different for each item. Do you need to match on both Coupon and Type?

    Also, in Sell, you have two rows with the same coupon number but different buyers. How can two buyers be on the same bill?

  5. #5
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    First, it would help if you describe what you are trying to calculate in column H. To do that I expect that you would also have to describe what Weight, Box, and Net, and Decrease/Increase mean.
    Column H in "Buy" is the weight of the container.
    Weight is the total weight of the product, including the container. First, I want to differentiate the product by it, but I'm afraid I may come into container with the same weight. So I was thinking of using the number of the bill which is unique.
    Net, is the net weight of the product, so it will be
    Please Login or Register  to view this content.
    It seems to me that you can't just match on any coupon number in Buy because the Net is different for each item. Do you need to match on both Coupon and Type?
    If it is possible, yes. but I think a match on the Coupon will be enough, also just in case a buyer will buy not the whole type of product. ie: the total net weight in "Buy" is 5515, the buyer only take 2000.

    Also, in Sell, you have two rows with the same coupon number but different buyers. How can two buyers be on the same bill?
    It's because we only use the bill for a reference. In circumstances, we open a new bill for the buyer. Sometimes the buyer only buy a type of the product, not the whole.

    Regards,
    Peter

  6. #6
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    *BUMP* So uh.. anyone ?

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Auto fill consecutive row after processing from different sheet.

    The lack of response means that people don't understand what you want.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Auto fill consecutive row after processing from different sheet.

    use this formula in H2 in sheet Sell:

    =F2-SOMPRODUCT((Buy!$B$2:$B$30=$C2)*(Buy!$D$2:$D$30=$D2)*(Buy!$F$2:$F$30))



  9. #9
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Quote Originally Posted by shg View Post
    The lack of response means that people don't understand what you want.
    I'm trying here

  10. #10
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Quote Originally Posted by snb View Post
    use this formula in H2 in sheet Sell:

    =F2-SOMPRODUCT((Buy!$B$2:$B$30=$C2)*(Buy!$D$2:$D$30=$D2)*(Buy!$F$2:$F$30))
    snb, your formula is trying to get a result from calculating the weight if the coupon and product type has a match between "Buy" and "Sell", am I correct?
    The formula I made in "Sell" H2 is correct in condition if we processed in the same rows.

    Based on your formula, Thanks to you I came to this:
    Please Login or Register  to view this content.
    Now.. moving on to "Cash Balance"
    How do we fill the date, from "Buy" and "Sell sheets to A2:A32 automaticaly?
    Each total from K2:K32 in "Buy" goes to Output
    Each total from I2:I32 in "Sell" goes to Input.
    Attached Files Attached Files

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Auto fill consecutive row after processing from different sheet.

    cfr. the attachment
    Attached Files Attached Files
    Last edited by snb; 01-03-2011 at 04:25 PM.

  12. #12
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Hi snb,
    What I'm trying to make will look like the this attachment.
    So, total from "Buy" or "Sell" will be put in sequence by the date in "Cash Balance".
    Each will be separated in different row.
    Attached Files Attached Files

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Auto fill consecutive row after processing from different sheet.

    cfr. the attachment.

    In the balance sheet all data form sell and buy will be inserted.(64 rows)
    After that you can sort this balance sheet (A1:F65) on date (menu/Data/sort).
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Quote Originally Posted by snb View Post
    cfr. the attachment.

    In the balance sheet all data form sell and buy will be inserted.(64 rows)
    After that you can sort this balance sheet (A1:F65) on date (menu/Data/sort).
    That won't work, snb. After I sort it out, the datas were mixed with each other.
    I need to keep every total from each dates (even the same date) to be in different rows.

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Auto fill consecutive row after processing from different sheet.

    That will definitely work if you sort correctly:
    select A1:D63, menu/Data/sort

    or using a macro

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Hello..
    Here's a comparison for the result.
    exclpic.xls is the temporary result.
    wiw.xls is the result I want.

    I hope you notice the differences:
    1. The dates are in ascending order, no matter where it takes. Either from "Buy" or "Sell", they're put in ascending order.
    2. If there more than 1 inputs on the same date, either "Buy" or "Sell", the data will be put in different rows. ie: row 4 and 5 (26 Mar). row 9 and 10 (29 Mar).
    3 And most importantly, there are no Input and Output in the same row. In C2, the total 74.625.500 which is from "Buy" went to Ouput.
    The same total from "Buy". All went to Output. (C4, C5, C6, C7, C8).
    4. For the Input, 75.600.000 in B3 is from "Sell". Please notice the right side of B3 is empty, as the left side of C2 is empty. It's because Input means profit which we got from "Selling", whilst Output means "Buying" the products.

    Sorry to confuse you with my language.
    Attached Images Attached Images
    Last edited by ganekon; 01-04-2011 at 11:30 AM.

  17. #17
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    Quote Originally Posted by snb View Post
    That will definitely work if you sort correctly:
    select A1:D63, menu/Data/sort

    or using a macro

    Please Login or Register  to view this content.
    Hi snb,
    I've change the formula after your ideas, thanks again. I think there is no other choice than to make it separate into double tables like the one you suggest. I have 1 last question, how do we fix the #VALUE! error in E37:E63 ?
    Attached Files Attached Files
    Last edited by ganekon; 01-04-2011 at 11:04 PM.

  18. #18
    Registered User
    Join Date
    12-29-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Auto fill consecutive row after processing from different sheet.

    nevermind, i've fix it. thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1