ForEach using Excel functions

  • Thread starter Thread starter armsiee
  • Start date Start date
A

armsiee

Hi,

Excel 2003.

Although reasonably au fait with VLookup function, I can't get it to
do what I want in this scenario.

I have a list of data in sheet1 which has a one to many relationship
with a set of data in sheet2. Data is joined by a reference number.

I want to have a flag on sheet 1 which is only set if a flag is a
condition is ment on sheet2 for every occurance of the reference
number.

I could simply acheive the task by using vba and looping through the
set of data to compare the fields, however is it possible to achieve
this task using Excel functions alone? From trawling through the
newsgroup I suspect I may need the Index and Match functions, but
haven't been able to accomplish it!

Thanks in advance

Simon
 
I'm not sure what this means:

" ... which is only set if a flag is a condition is ment on
sheet2 ..."

so I'm not sure what you want.

In sheet2 you could have a formula like this:

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

copied down, assuming your reference number is in column A. This will
give you a sequential number for each reference number like this:

ref1_1, ref1_2, ref2_1, ref1_3, ref3_1, ref2_2 ...

and so on. From this you can pick up all the values that refer to a
particular reference number by means of INDEX/MATCH formulae in
sheet1. Give some further details of your data layout if you need some
more assistance.

Hope this helps.

Pete
 
=COUNTIF(RefRange,RefVal) = SUMPRODUCT((RefRange=RefVal)*(CondRange=CondVal))

Something like:

=COUNTIF(Sheet2!A2:A100,"Ref1") = SUMPRODUCT((Sheet2!A2:A100="Ref1") )*(Sheet2!B2:B100="Cond1"))


HTH,
Bernie
MS Excel MVP
 
Hi,

Try this

=SUMPRODUCT(--(Sheet2!E1:E11=A1),--(Sheet2!F1:F11="Y"))=COUNTIF(Sheet2!E1:E11,A1)

In this example we are checking to see if A1 is in the second sheet and then
to see if the flag is set (here my flag is "Y" and is in column F.) Then I
am check to see if that count is equal to the total count of A1's on sheet2,
if it is the formula returns TRUE otherwise FALSE. You can bracket this with
an IF to get your flag:

=IF(SUMPRODUCT(--(Sheet2!E1:E11=A1),--(Sheet2!F1:F11="Y"))=COUNTIF(Sheet2!E1:E11,A1),"My Flag","")
 
Sorry ,I meant to say if a "condition is met on each occurence on
sheet2"

Sheet 1 is a summary, sheet 2 is the detail. Sheet 1 can only be
marked as complete if all the detail in sheet 2 is complete. i.e ref
50 exists on sheet 1, and has 3 occurence on sheet 2. I want to
search down sheet 2 and check for each occurence of ref 50 and say
check column E to see if the flag is set to complete, then find the
second occurence, and then the third if all are set then I can mark
sheet 1 as complete.

Hope this makes more sense, and thanks for the contributions I will
test them out and report back.
 
Hope this makes more sense, and thanks for the contributions I will
test them out and report back.
Click to expand...

For the record, Shane's solution did excactly what i needed.

Just a quick follow up please will you explain the significiance of
the '--' notation? I presume it has something to do with the array
element of the function? I could not see any reference to it in the
help file.

Thanks all for your help.

Simon
 
An expression like:

(Sheet2!E1:E11=A1)

will return a series of True or False for each of the values in
E1:E11. Using the double unary minus in front of this then changes
these Logical values into numeric values of 1 or 0, so that SUMPRODUCT
can use them arithmetically.

Hope this helps.

Pete
 
Back
Top