# Thread: Excel Comparison Function (Excel 2000)

1. ## Excel Comparison Function (Excel 2000)

Hope someone can help me with this one... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

I have a headcount report that I generate every month. The problem is that the data changes month to month so I would like to compare the data to a column called "Depts". I would like the function to tell me which depts are new so that I can create any formulas necessary.

Please let me know if you need more information. Any information would be helpful.

Thanks a bunch! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

2. ## Re: Excel Comparison Function (Excel 2000)

Can you give us a sample workbook that shows what you have?

3. ## Re: Excel Comparison Function (Excel 2000)

Hello Legare Coleman,

Here is the sample workbook. You'll need to unzip it.
Thanks a bunch for looking at it and helping me out <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

4. ## Re: Excel Comparison Function (Excel 2000)

Take a look at the formula in cell AZ5 of the attached workbook and see if it will point you in the right direction.

5. ## Re: Excel Comparison Function (Excel 2000)

The solution is close. However, the problem is I need to know which tiers do not exist in my formulas that exist in the data. Therefore, it's the solution you gave me but backwards.

Is this possible?

<img src=/S/help.gif border=0 alt=help width=23 height=15>

6. ## Re: Excel Comparison Function (Excel 2000)

Sorry, I do not understand what you are asking. Are the tables out in AY1:BP14 repeated many times, once for each Tier number, and are you looking for which tier number back in D225 don't appear in one of the cells like AZ4 or AZ14?

7. ## Re: Excel Comparison Function (Excel 2000)

Yes, AY1:BP14 appear many times for each of the tiers that exist in the data. So you are correct, I am looking for the tier numbers in D225 or B2:B25 or F2:F25 or H2:H25 that don't appear in one of the cells like AZ4 or AZ14.

Sorry about making it so confusing. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

8. ## Re: Excel Comparison Function (Excel 2000)

I would need a bit more information, and I think it will require a VBA macro to accomplish this. I would need to know the following:

1- What does the workbook look like with more than one of the AY1:BP14 ranges.

2- Which cells can the tier number appear in (AZ4, AZ14, etc in each of the ranges.

3- How do you want the number that do not appear shown, a list somewhere, hilighted in color in the original list, something else?

9. ## Re: Excel Comparison Function (Excel 2000)

Unfortunately the report is too big to submit so I will try to explain it the best way I can..

I have created an AY1:BP14 for each of the tiers straight down. The cells that have the tier numbers are as follows (for timesaver, they all begin with AY):
AY4, 12, 15,16,19,22,26,34,42,45,49,52,55,61,65,70,73,76,79 ,87,90,93,96,99,102,105,108,111,114,117,120,123,12 6,129,132,135,138,141,144,147,150,153,156,159,167, 175,183,191,199,207,215,223,231,239,247,255,263,27 1,279,287,295,and303

As for question three - I would like it to give me a list of the new ones but I am flexible. Whichever way you may find easiest. I am also open for suggestions.

10. ## Re: Excel Comparison Function (Excel 2000)

This brings up a couple of additional questions:

1- Do you mean AY or AZ? AY in the workbook you sent seems to contain row lables, not Tier numbers.

2- You list row 12 as one to be compared? In the workbook you posted, AZ12 contains "TIER1", not a number, and AZ14 looks like it contains a Tier number, but row 14 is not in your list. are you sure the list of rows you gave is correct?

3- Can I just compare all of the Tier numbers from B, D, F, H, J, L, and N to all of the cells in column AZ, or is there a possibility of getting a hit on one of the rows not in your list?

11. ## Re: Excel Comparison Function (Excel 2000)

Hello Legare,

You are absolutely right, it should be AZ...and yes, it should be AZ14,Sorry for the error. I quess i've worked on this a bit to long..I am mixing up all of the columns,functions etc <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

question 3 - I believe it would be okay to compare columns B, D, F, H , L, and N to the column AZ. But, there is a possibility that their is an AZ tier that does not exist on the data (B,D,F,H,L,or N). However, this would be okay because my calculations will not be incorrect. Those will
only show up as zero.

Thanks so much. <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15>

12. ## Re: Excel Comparison Function (Excel 2000)

I have noticed that you have another problem in the workbook that you sent. Some of the Tier numbers are entered as text and some are entered as numbers. I have attached a workbook with a macro that I think does what you need. It make a list in a new worksheet named "Missing". I have programmed the macro to get around the Text/Number problem, however, this could cause you other problems if you don't change it to be consistent.

13. ## Re: Excel Comparison Function (Excel 2000)

The data will actually be all text because it's coming from a mainframe system. In re-creating the example, I inadvertently typed the tiers making them numbers. I apologize for that. Should I change anything on the macro?

I will be placing the macro onto the actual report and I will get back to you. But, from looking at it quickly, I think you got exactly what I needed.
<img src=/S/bow.gif border=0 alt=bow width=15 height=15>

I will get back to you tomorrow.

Thank you very much and have a good night. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

14. ## Re: Excel Comparison Function (Excel 2000)

No, if they are all text, the macro should work as is. If they were all going to be numbers I might change a few things.

When you put the macro in the other workbook, don't forget to create another worksheet and name it "Missing".

15. ## Re: Excel Comparison Function (Excel 2000)

Hello Legare,

Thanks a Million <img src=/S/exclamation.gif border=0 alt=exclamation width=7 height=22>, the VB macro is exactly what I was looking for. You are amazingly brilliant !!!!

My only question would be:
The VB program takes approximately 30 minutes to run completely. Is this the norm for large amounts of data?

Thanks again you were verrrrry helpful. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•