How smart is Excel?

[Deb G.]

I know Excel can do almost everything except cook my dinner but....I can't figure out if Excel can do the following (and if the answer has anything to do with Visual Basics I'll have to run screaming from the building!): In one workbook I have various tabs allotted to the months of the year. Each month contains a list of client names. As each month is completed, is there a way for Excel to tell me if this is a repeat name and better yet, how many times it has been repeated in the past months?

 

[Juan Pablo González]

Mhmm, the usual approach to handle duplicates involves COUNTIF... or a SUMPRODUCT formula if it has more than 2 conditions.

 

[Deb G.]

Thanks Juan.

I'm only marginally familiar with some of these functions. I'n not sure I understand how one of the two functions you mentioned bring about the result that I want. For instance, what criteria am I putting in the range for COUNTIF? Would a LOOKUP table need to be created first??? I guess I really don't know where to start with this thing. Thanks for your help.

Deb

 

[Brian from Maui]

Deb,

=COUNTIF(RANGE,"EMPLOYEE NAME") for a single criteria

=SUMPRODUCT((RANGE,=CRITERIA 1)*((RANGE,=CRITERIA 2))
Both ranges must be the same

 

[Yogi Anand]

Hi Deb:

Let us say your worksheets are named Jan, Feb, Mar, .... and the Names appear in column A

You could create a Summary sheet to check if a Name that you are interested in has appeared in prior months. See the worksheet simulation:

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - Book1</FONT></TD></TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>E7</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Month</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Occurrence</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>John</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Jan</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=COUNTIF(Jan!A:A,$D$1)')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Feb</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=COUNTIF(Feb!A:A,$D$1)')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Mar</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=COUNTIF(Mar!A:A,$D$1)')><FONT FACE=Arial COLOR=#000000>0</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Summary</U></TD></TR></TABLE>
<FONT COLOR=#339966>Click on ahyperlinked cell to see the underlying formula </CENTER>

Please post back if it works for you ... otherwise explain a little furthe ranf let us take it from there.

Regards!

 

[Aladin Akyurek]

On 2002-06-24 11:39, Deb G. wrote:
I know Excel can do almost everything except cook my dinner but....I can't figure out if Excel can do the following (and if the answer has anything to do with Visual Basics I'll have to run screaming from the building!): In one workbook I have various tabs allotted to the months of the year. Each month contains a list of client names. As each month is completed, is there a way for Excel to tell me if this is a repeat name and better yet, how many times it has been repeated in the past months?

=COUNTIF.3D(Jan:Mar!$A:$A,A1)

in B1 of Apr will give you the desired count regarding the name in A1 in sheet Apr.

This function is available in Longre's Morefunc add-in, downloadable at:

http://longre.free.fr/english/index.html

Take a look at the so-called 3d functions in the Help file in order to assess why COUNTIF.3D would fit your situation.