+ Reply to Thread
Results 1 to 6 of 6

Best way to solve new problem!

  1. #1
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275

    Best way to solve new problem!

    Hello guys, not been around for a while but need help to find best solution to this problem.

    I have a worksheet in which I input main data for use with other worksheets.

    There about 8,000 lines of data currently in this worksheet with information in 5 columns.

    1) Date
    2) Customer Number
    3) Item Number
    4) Amount Paid
    5) Residual


    Columns 1-4 contain data on every line but column 5 (Residual) only contains data as and when required.

    From another worksheet, I need to automatically input the 'Residual' value but only when the 'Date' + 'Customer Number' + 'Item Number' are true, as dictated by inputs in the 2nd worksheet.

    I would sum up the equation as follows:

    If 'Date' + 'Customer Number' + 'Item Number' on 2nd worksheet occur on the same horizontal line in 1st worksheet; then output 'Residual'.

    Have tried various combinations but none have been successful.

    All suggestions welcome ...thanks!

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Spellbound
    Hello guys, not been around for a while but need help to find best solution to this problem.

    I have a worksheet in which I input main data for use with other worksheets.

    There about 8,000 lines of data currently in this worksheet with information in 5 columns.

    1) Date
    2) Customer Number
    3) Item Number
    4) Amount Paid
    5) Residual


    Columns 1-4 contain data on every line but column 5 (Residual) only contains data as and when required.
    Columns are A, B, C, D & E, . . . Rows are 1-4

    From another worksheet, I need to automatically input the 'Residual' value but only when the 'Date' + 'Customer Number' + 'Item Number' are true, as dictated by inputs in the 2nd worksheet.

    I would sum up the equation as follows:

    If 'Date' + 'Customer Number' + 'Item Number' on 2nd worksheet occur on the same horizontal line in 1st worksheet; then output 'Residual'.

    Have tried various combinations but none have been successful.

    All suggestions welcome ...thanks!
    In sheet 2, E2 put

    =SUMPRODUCT(--(Sheet1!A$2:A$8000=A2)*(--(Sheet1!B$2:B$8000=B2))*(--(Sheet1!C$2:C$8000=C2))*Sheet1!D$2:D$8000)

    and formula fill downwards.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    Hi Bryan

    Thanks for that tip but you made me realise that I have not done a very good job of explaining the problem.

    Firstly, the figures entered on worksheet 1 are all manual inputs. These particular columns are not used in a calculation, however they are also data for a Pivot Table.

    So whilst I used the '+' sign in summing up, it was not meant to imply that the figures required a total.

    Perhaps a better way of describing the problem is as follows:

    If 'Date' & 'Customer Number' & 'Item Number' on 2nd worksheet occur on the same horizontal line in 1st worksheet; then output the contents of the cell 'Residual' from the same line.

    Hope this clarifies the situation better ...thanks

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Spellbound
    Hi Bryan

    Thanks for that tip but you made me realise that I have not done a very good job of explaining the problem.

    Firstly, the figures entered on worksheet 1 are all manual inputs. These particular columns are not used in a calculation, however they are also data for a Pivot Table.

    So whilst I used the '+' sign in summing up, it was not meant to imply that the figures required a total.

    Perhaps a better way of describing the problem is as follows:

    If 'Date' & 'Customer Number' & 'Item Number' on 2nd worksheet occur on the same horizontal line in 1st worksheet; then output the contents of the cell 'Residual' from the same line.

    Hope this clarifies the situation better ...thanks
    No, but no matter, did it work?

    I presumed that there was only one figure per Date/Customer/Item required. Is this the case?

    If so then it should have worked, if not what results did you obtain compared to what you expected?

    ---

  5. #5
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    Hi Bryan

    Unfortunately your solution did not work, which may be due to the way that I have tried to explain the situation.

    I created a mini version of my original input worksheet using 10 lines of data.

    As before columns A, B, C, D, E are as shown below.

    A) Date
    B) Customer Number
    C) Item Number
    D) Amount Paid
    E) Residual

    Row 1 is the header row and the remaining 9 rows contain data. All rows contain data appertaining to columns A-D but column E only has data where applicable. Thus there is only one combination of all 3 entries in columns A-C to justify input into coulumn E.

    The cell containing the formula on Sheet2 will be in column F for example but may be located at any row within this column.

    So, when I input your formula in cell F21 on Sheet2 for example; the result is the data contained in cell D2 on Sheet1. In the same way, if I amend your formula to take the information from column E2-E10; the result is from cell E2.

    Hope this clarifies the situation better ...David

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Spellbound
    Hi Bryan

    Unfortunately your solution did not work, which may be due to the way that I have tried to explain the situation.

    I created a mini version of my original input worksheet using 10 lines of data.

    As before columns A, B, C, D, E are as shown below.

    A) Date
    B) Customer Number
    C) Item Number
    D) Amount Paid
    E) Residual

    Row 1 is the header row and the remaining 9 rows contain data. All rows contain data appertaining to columns A-D but column E only has data where applicable. Thus there is only one combination of all 3 entries in columns A-C to justify input into coulumn E.

    The cell containing the formula on Sheet2 will be in column F for example but may be located at any row within this column.

    So, when I input your formula in cell F21 on Sheet2 for example; the result is the data contained in cell D2 on Sheet1. In the same way, if I amend your formula to take the information from column E2-E10; the result is from cell E2.

    Hope this clarifies the situation better ...David
    No, and it's not what I expect.

    Try the attached. Where you put the formula matters little, but it is set for Row 2, so unless you put it on row 2 you will need to modify the test ranges.

    I expected the formula to be put on row 2 and then formula-fill down the column to the extent of your data on sheet 2, as per the attached.

    The formula should then show the figure from Sheet1 that corresponds to the combination of A&B&C for the row in which the formula is (ie, on row 27 then Sheet2!A27 Sheet2!B27 and Sheet2!C27 are used to search Sheet1 for a figure).

    Is that what you expected? - and is that the result that you get?

    ---
    Attached Files Attached Files
    Last edited by Bryan Hessey; 10-24-2006 at 07:57 PM.

+ 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