Find Unique row and sum and other questions

Originally Posted by ahmedwaseem2000

Hi,

Question 1) I need to find the unique column values and sum the next column values. i,e


Question 2) in the above example, if columnid value is already present and the columnXXX value entered is not matching the previous value then an error message should be displayed.

for instance.


Question 3)

consider this example:



In the above example for the columnID for the first time it is 100 - 50 = 50 and 50 is displayed in the result section. and for the second time for the same columnid 1 it is 100-75(i,e 50+25) = 25 and the same value is displayed in the result section.

Please be kind enough to help out with these issues( I have no knowledge about how to achive it) if i have violated any of the forum rules please let me know so that i could amend it.

Many thanks in advance.

Regards,
Waseem

The first part can be covered with

Please Login or Register  to view this content.

I am still trying to decipher the second part.

hth
---

added

the 100 goes 50 - 50 then 75 - 25
would the next 100 go 87.5 - 12.5 ?

the 200 goes 100 - 100 then 50 - 50

different pattern

the 500 goes 250 - 250

would the next 500 go 375 - 125 ?

the 600 goes 400 - 200

different pattern.

What is the pattern for Column YYY and Result?

What relationship does the pattern have to the first question re Unique numbers?
unique (first unique) would be rows 1, 2, 4 & 5

1 = 100, 2 = 200, 4 = 500, and 5 = 600

------

Originally Posted by ahmedwaseem2000

Let me try to explain you. ColumnID is the column with transaction id value for which payments are made in 2 or 3 shot's.

for instance, for transaction id, the total transaction value is 100( ColumnXXX value) and 50 is the value received for the first time (ColumnYYY value) . and on a later date again a value is received i,e 25 so the total valued recieved is 75 which should be subtracted from the total transaction value. i,e 100 - 25 =25 should me mentioned in the second row of columnID 1.

also, i want if the result value is a positive number then it should go to the Result column and if it is a negative then it should be moved to minus column. In the example of the id 1 it has gone minus in the third transaction.

Thank you very much for your help and hope i am clear enough to you.

Totally not understodd, - do you want to accumulate all amounts paid (column B) that relate to the amount in column A?

if so, with the formula in D as

=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

in E put

=SUMPRODUCT(--(A$1:A$10=D1)*(--(B$1:B$10>0))*B$1:B$10)

--

Well, Sorry for being vague!!!!. Ok, here we go again.

for the first question i have managed to get the solution. Here is the answer for all the forum user reference(this is not my solution).

find the value and keep it in another cell (Z2)

Please Login or Register  to view this content.

this will sum all the values:

Please Login or Register  to view this content.

For the second question:


I want to verify for a given transaction id in column 2 if the value put is similar to that of the pervious entry or not. If the value is not matching then throw an error and clear the cell.


For question 3:

I would try with more examples ( atleast 3)

first one:

<ColumnID> <ColumnXXX> <ColumnYYY > <Result> <Minus>
001 1000 500 500(1000 - 500) 0
002 5000 1000 4000( 5000 - 1000) 0
003 100 100 0 0
002 5000 3000 1000(5000 - 3000 -1000 previously received) 0
001 1000 1000 0 (1000-1000-500 previously received )

Explanation 1:
for the transaction id 001 the payment made is twice. first time it is 500 and the second time it is 1000( which is excess) so in the first row the Minus column value would be 0 because 1000 - 500 = 500 which is greater than zero. and in the second transaction(row 5) a payment of 1000 is received for the same transaction id ( remember that we had already received a payment of 500 before). so the total payment received is 1500 and the transaction amount was 1000 only so, the amount received is extra, hence, in the result section, the value displayed in the 5th row(second time payment recieved) is 0 and value displayed in the minus section is 500( no minus sign).

it is something, to find over due or under due of a particular transaction.

Explanation 2:

Taking the transaction with transaction id 002

the total amount of transaction is worth 5000(ColumnXXX), and the first time the amount received is 1000 ( ColumnYYY) so in the result column for 002 transction 5000- 1000 =4000 is shown. and in the minus column, since 4000 is a positive number so 0 is displayed. in the second time payment of 3000(columnYYY) is made so we have received a total of 3000 + 1000 = 4000, hence, 5000 - 4000 =1000 is displayed in the result column. and again, since, 1000 is positive so, in the minus column 0 is displayed.

I have tried harder this time to explain my situation. Please let me know if still i am not very much clear.

Thanks for your Help.

Originally Posted by ahmedwaseem2000

Well, Sorry for being vague!!!!.

There is NO column XXX nor YYY in my version of Excel, my columns (in Excel 2003) are A to IV, please let me know which version of Excel you have.

Ok, here we go again.
for the first question i have managed to get the solution. Here is the answer for all the forum user reference(this is not my solution).

find the value and keep it in another cell (Z2)

Please Login or Register  to view this content.

More simple was my formula

=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

as this can be formula-filled down as far as required without the need to keep amending the range of the formula. Noted that Z2 (ie, Z) is the column used by you.

this will sum all the values:

Please Login or Register  to view this content.

to total - use

=Sum(AA$2:AA200)

where Z200 indicates your last row for data. See AA formula later.

For the second question:

I want to verify for a given transaction id in column 2 if the value put is similar to that of the pervious entry or not. If the value is not matching then throw an error and clear the cell.

For question 3:

I would try with more examples ( atleast 3)
first one:
Explanation 1:
for the transaction id 001 the payment made is twice. first time it is 500 and the second time it is 1000( which is excess) so in the first row the Minus column value would be 0 because 1000 - 500 = 500 which is greater than zero. and in the second transaction(row 5) a payment of 1000 is received for the same transaction id ( remember that we had already received a payment of 500 before). so the total payment received is 1500 and the transaction amount was 1000 only so, the amount received is extra, hence, in the result section, the value displayed in the 5th row(second time payment recieved) is 0 and value displayed in the minus section is 500( no minus sign).

it is something, to find over due or under due of a particular transaction.

Explanation 2:
Taking the transaction with transaction id 002
the total amount of transaction is worth 5000(ColumnXXX), and the first time the amount received is 1000 ( ColumnYYY) so in the result column for 002 transction 5000- 1000 =4000 is shown. and in the minus column, since 4000 is a positive number so 0 is displayed. in the second time payment of 3000(columnYYY) is made so we have received a total of 3000 + 1000 = 4000, hence, 5000 - 4000 =1000 is displayed in the result column. and again, since, 1000 is positive so, in the minus column 0 is displayed.

I have tried harder this time to explain my situation. Please let me know if still i am not very much clear.

Thanks for your Help.

Did the formula supplied work?

I have used in this example,
column A = ID
column B = Amount Issued
column C = amounts repaid
column Z = unique ID list
column AA = unique 'Issued' list
column AB = unique total of repayments per ID
column AC = amount remaining to be repaid
column AD = amount to be refunded (overpaid, negative)
Row 1 = headers row
Rows for data assumed to be 2 to 200

with the formula in Z2 of:

=IF(COUNTIF(A$2:A2,A2)>1,"",A2)

you will have, in column Z, a list of all transaction id's

in column AA, at AA2 put

=IF(COUNTIF(A$2:A2,A2)>1,"",B2)

you will have in column AA a list of all issued amounts,

then, to find the payments relating to those:, in column AB, at AB2 put

=SUMPRODUCT(--(A$2:A$200=Z2)*(--(C$1:C$200>0))*C$1:C$200)

where A: is the column containing the ID
Z: is the column with the formula shown above
C: is the column containing the repayments (your columnYYY ?)

amend the '200' to be the number of rows of data in your sheet.

When you have placed the formula, then formula-fill down the sheet as far as your data goes (shown as 200)

(note, formula fill is described at http://www.mvps.org/dmcritchie/excel/fillhand.htm)

To get the 'Result' (ie, the balance remaining to be paid) - in AC2 put

=If(AB2>AA2,"",AA2-AB2)

and to get the negative: - in AD2 put

=If(AB2>AA2,AA2-AB2,"")

and, again, formula fill downwards.

Hope this helps
---

Originally Posted by ahmedwaseem2000

Bryan - here is the attached file where i have tried to implement the formula's passed by you.

In the sheet "Outstation sales" i have tried to implement the formula's provided by you, I wasn't able to implement them. However, In sheet2 i have made the example sheet how it shall be looking like.

Also, would it be possible, like, when the Id is already existing and when the issued amount is changed then to throw an error?

Thanks in advance.

Regards,
Waseem

Waseem,

my apologies, after stating that the ranges used in a Sumproduct must be equal, I started the C range at 1 instead of 2.

Try now

Note, the new formula for AC2 is

=IF(OR(AA2="",AB2>AA2),"",AA2-AB2)

to allow formula-drag across the blanks.

"Also, would it be possible, like, when the Id is already existing and when the issued amount is changed then to throw an error?"

=COUNTIF(A:A,A2)-SUMPRODUCT(--(A$2:A$4=A2)*(--(B$2:B$4=B2)))

will show non-zero if this occurs.
---

Originally Posted by ahmedwaseem2000

Bryan - Thanks a lot for your extended Help!!!!!!

Well, I want the difference amount to be shown in the last entry of the id. is is possible??? and let the previous entries to be as it is???

I have attached the example where the "Amount to be refunded" column (E2) the value shown is -130000, I would want that to be displayed in E5 and in E2 it should be 120000 and in E3 it should be 20000.



Thank You Very Much.

Warm Regards,
Waseem

the formula for AB2 is

=SUMPRODUCT(--(A$2:A$6=Z2)*(--(C$2:C$6>0))*C$2:C$6)

(not 2:5 )

Perhaps we can use a Count in place of the $6 - or

=SUMPRODUCT(--(A$2:A$6000=Z2)*(--(C$2:C$6000>0))*C$2:C$6000)

and in Tools, Options, View untick the 'Zero Values'

---

Originally Posted by ahmedwaseem2000

I guess i was not very much clear in my previous post:

Well, I need to capture the current status of each record and also the history of the record. that is, in the attached example for the 7th row ( row with total) i have the formula and its working perfectly fine. However, with your formula it is giving me only the final value but it is not managing the history of the record. that is, on a particular time what was the amount due. I need the formula to calculate the values in the column's D and E as defined in the example.

It would be of a GREAT HELP FROM YOU, BRYAN!!!!

your $40,000.00 at D4 would appear to be in error, otherwise should be ok

---

Originally Posted by ahmedwaseem2000

Another issue with the file that you have attached in your previous post is not applying the formula to the new rows inserted. the new row would be inserted before the row with the "total".

This is more a feature of Excel than an 'issue', ie, if you insert more rows and want formulae to include those rows you would need to adjust your formula and re-formula fill.

To adjust the formulae to include inserted rows you would need, for each row inserted, to either amend the formulae at row 2 and formula fill to the current data level, or, add a macro to do this and invoke the macro to insert a new line and modify the required formulae.

and also, I need to introduce a new column called tax which should be calculated only once. like, for the first time when the new entry is made the tax should be calculated. and for the sub-sequent entries the tax should not be calculated.

the formula

=IF(COUNTIF(A$2:A2,A2)>1,"",B2*0.1)

where 0.1 indicates you tax rate, should work. This formula will also need to be formula filled for each new inserted row.

and another functionality is, whenever, the ID value is repeated then the amount issued should be picked from the previous entry made.

Did you mean 'previous entry' or 'original entry'?

Do you mean that you are now changing your data such that column B will contain the issued amount only once, and you need to adjust the formula for columns D E F Z AA AB and AC to suit? Doesn't this conflict with the original question for this thread "Question 1) I need to find the unique column values and sum the next column values. i,e and would this not create a problem for your second question Question 2) in the above example, if columnid value is already present and the columnXXX value entered is not matching the previous value then an error message should be displayed"

Thanks again!!!