how to get an N/A or error for crosschecking when using sumproduct or multiple condition

[apelsunkist]

i have this kind of data as reference. where the bill could be not-sorted in any order. [TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5025[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5021[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4045[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8608[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4046[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5023[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5023[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5024[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

i want to get some data from that table and put it into another table as this :
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5021[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5022[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5023[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5024[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5025[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

which is more neat, sorted, and already separated into each first digit of the bill number. im going to separate the data from the first table into several separated sheet based on the first two digit of the bill number. the first two digit of the bill number define which bill-book it's belong.

i want to fill the data in the G column from the value in the B column, based on the criteria of the A column that meet the exact as in the F column.

so i'm using :

=SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9))

it does fill the G column with the value from the B column.

the result is
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5021[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5022[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5023[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5024[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5025[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]

the thing is, every bill number (in column A) could only have one value. as it should be. For each bill number, there could only be one transaction. so you could only use one bill number once. if the same bill number shown twice or more in the first table (column A), than it must have be a typo (or worse, a fraud =P).

what i want is, if there is a bill number that shown twice or more in column A, than it would result in N/A or error in column G.
as wise if there is a bill number that is not shown in column A (e.g bill number 5022) it would also resulting in N/A or other error sign.

by looking the N/A or error sign i could know that there is some wrong typo in the data from the first table. and i could know which data i should clarify and corrected.

the sumproduct formula i used, give adding when there is a bill number repeated (bill number 5023), instead of N/A. and also give 0 when there is a bill number that is not written (bill number 5022), instead of N/A.

i have try

=INDEX($B$2:$B$9;MATCH($F2;$A$2:$A$9;0);0)

which i dont actually understand how does it works =P. but it does give me N/A in the bill number that is not writted (bill number 5022).
yet for the bill number that shown twice (bill number 5023), instead of an N/A, it still fill the column G with the first data of the bill number 5023 (3).

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5021[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5022[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5023[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5024[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5025[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]


the result that i want is, more or less :
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5021[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5022[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5023[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5024[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5025[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

i actually dont understand Pivot Table and Macro at all. so i would rather stick with a formula if i could.

is there a formula to get a result as described above? any kind of help would be great

and what options i could use, to find a numbers that is shown twice or more in the A column? MODE formula could return the data that is most frequently occuring, its true. but only one single reasult. in my data there could be more than one bill number that is typed wrong and might shown more than one time.

if you could help me solve either the two problem (especially the first one), i think i could finally able to delegate the writing job to others with ease at heart. and it does really a weight-lifting
well if there is anyone who could help me, im pretty sure that he/she is in this forum
im using Win-7 and Excel-2007.

regards,
riza rizki

 

[JoeMo]

Maybe something like this:

=IF(COUNTIF($A$2:$A$9,$F2)>1,NA(),IF(SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9))=0,NA(),SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9))))

 

[apelsunkist]

Maybe something like this:

=IF(COUNTIF($A$2:$A$9,$F2)>1,NA(),IF(SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9))=0,NA(),SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9))))

thanks Joe i'll try it :D

 

[apelsunkist]

it turn out it works great, thanks Joe. :D
the formula does result in N/A when the number is repeated or not existed.
but i think i forgot to mention that the value in B column could also be zero (and a lot of them is indeed zero =P). well zero value is acceptable, as long the bill number is still written there (as indicated in Cell B5, bill number 8608).
so could you show me how to modify the formula, so that it would only result in N/A, when :
1. the bill-number in column A is repeated more than once (which has been solved by the formula you gave)
2. the bill-number in question from column F, is not exist in column A. (e.g : Cell F3, bill number 5022), result in N/A. wich is also have been solved by that formula.
3. and still resulting in zero (0), if the bill-number does exist and have zero as value.
so that if we change the Cell B2 into zero, the Cell G6 would result in zero instead of N/A.
could we actually do that much?

so the result would become like this, if we change the value of B2 into zero :
[TABLE="class: cms_table_grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]5021[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]5022[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5023[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5024[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5025[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[apelsunkist]

if it could make it more easier, there are another column (Col C). which writen in a text. if the column B have a value, the column C might have a text string, and might not. but the bill number in column B is existed but has a zero value, then the column C had to always have a text string. as seen like :
[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Number[/TD]
[TD]Cloth Value
[/TD]
[TD]Other Sell
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5025[/TD]
[TD]0[/TD]
[TD]1 Bedcover[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5021[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4045[/TD]
[TD]5[/TD]
[TD]3 Blanket[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8608[/TD]
[TD]0[/TD]
[TD]2 Bedcover[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4046[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5023[/TD]
[TD]3[/TD]
[TD]1 Bedcover[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5023[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5024[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so if it be like this, could we seperated the zero-but-existed value from the non-existed bill number?

thank you

 

[apelsunkist]

CASE CLOSED. thank you

found it. ^____^
i got Joe formula, modified into :

=IF(COUNTIF($A$2:$A$9;$F3)>1;NA();IF(SUMPRODUCT(($A$2:$A$9=$F3)*($B$2:$B$9))=0;IF(SUMPRODUCT(($A$2:$A$9=$F3)*($B$2:$B$9))>1;$B$2:$B$9;IF(ISTEXT(INDEX($C$2:$C$9;MATCH($F3;$A$2:$A$9;0);0))=FALSE;NA();SUMPRODUCT(($A$2:$A$9=$F3)*($B$2:$B$9))));SUMPRODUCT(($A$2:$A$9=$F3)*($B$2:$B$9))))

it serve well on every conndition i would want it.

then i read aloooot of thread in the forum. and that inspired to:

=IF(COUNTIF($A$2:$A$9;$F4)>1;NA();INDEX($B$2:$B$9;MATCH($F4;$A$2:$A$9;0);0))

which suprisingly could result in the same manner (with only so less term =P)

thanks Joe and every one.
Mr. Matty advice (in some other thread) is really great. more or less is about : try to use evaluate formula tool in excel.
i've never though it would be soooo usefull. 'usefull' even not enough to describe it.
it's so suprising that it is there and yet i've never even give glance to it

(case closed)