# Best way to solve new problem!

1. ## 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. 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
---

3. 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. 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. 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. 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?

---