VBA Vlookup with calculation - no duplicates

**Cboggie** · 01-24-2014, 09:43 AM

Hi,

I have a sheet that I am trying to get some metrics on and need to do them with VBA. Basically, I need to get the SUM of all "Ratings" where the status is "On" and exclude any duplicate "Number" (including the rating only once per that "number") . I'll also need the total amount of rows that got calculated (for the next calculation)

After it has the SUM and number of rows:

I want it to SUM up the "rating" for the "number of rows" calculated in the above calculation (excluding duplicates).

Further explanation is in the sheet provided.

Thanks

**Cboggie** · 01-26-2014, 09:47 AM

Bump - Anyone?

**jaslake** · 01-26-2014, 12:58 PM

Hi Cboggie

In all instances except one you've highlighted the first occurrence of duplicate items. The exception is 117.75...any reason?

**Cboggie** · 01-26-2014, 02:09 PM

Originally Posted by jaslake

Hi Cboggie

In all instances except one you've highlighted the first occurrence of duplicate items. The exception is 117.75...any reason?

Nope, as long as it includes one, its good

**jaslake** · 01-26-2014, 03:06 PM

Hi Cboggie

This Code is in the attached...please see my Notes in the File...click the Button to fire the Code

Please Login or Register  to view this content.

**Cboggie** · 01-26-2014, 03:48 PM

Yes!

That works perfect. Thanks jaslake.

**jaslake** · 01-26-2014, 03:59 PM

You're welcome...glad I could help. Thanks for the Rep.

**jaslake** · 01-26-2014, 06:15 PM

You're welcome...glad I could help.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Cboggie** · 01-26-2014, 08:20 PM

After trying to integrate it into my current sheet, I'm having all kinds of problems. Here is a new sheet that may help me integrate it more into the bigger one.

Same principle with a few changes.

I'll need to copy it to other sheets (possibly use Active.sheet?)
I may need to add up to 8 status' to include/exclude

Thanks!

**jaslake** · 01-27-2014, 05:37 PM

Hi Cboggie

In the original File we were excluding duplicate Index Numbers in the first calculation...in this File you have indicated this

Same as last time, exclude duplicate "Number" while calculating the "Index" SUM for everything else that has a status of (On or Inprg).

Which is it Duplicate Index Numbers or Duplicate Numbers??

I don't understand this...run through it step by step...you want the calculation placed in Cell J27 of a new Sheet and then delete that new Sheet??

For the pasting , I think it would be better if it would create a new sheet then delete it after the calculation is finished. Putting the results in J27.

What are all these possible combinations

Status' to include/exclude will change and possibly have 2-8 different ones that I'll need to change

**Cboggie** · 01-27-2014, 05:41 PM

Excluding "Numbers"

J27 of the same sheet. The sheet that we copy/paste to, can be deleted.

With the all the status', i may need to change them...thats all. Just thought is was worth mentioning.

thanks

**jaslake** · 01-27-2014, 05:54 PM

Hi Cboggie

Either we're not communicating or this is a change

Excluding "Numbers"

In the Original File, for the First Calculation, we were eliminating Duplicate Index Numbers (Column A). In the Second Calculation we were eliminating Duplicate Numbers (Column C).

If this IS a change...show me your expected output and how you arrived at it.

Regarding this...what are they...ALL of them....don't care to rewrite the Code yet again...

With the all the status', i may need to change them

**Cboggie** · 01-27-2014, 06:52 PM

Originally Posted by jaslake

Hi Cboggie

Either we're not communicating or this is a change
In the Original File, for the First Calculation, we were eliminating Duplicate Index Numbers (Column A). In the Second Calculation we were eliminating Duplicate Numbers (Column C).

Yes, you are right. I changed it to look at duplicate "numbers". We are only worried about duplicate "numbers", not duplicate "index". Duplicate "indexes" are ok, as long as they don't have the same "number"

Originally Posted by jaslake

If this IS a change...show me your expected output and how you arrived at it.

Let me know if that makes sense, or i need more clarification

Regarding this...what are they...ALL of them....don't care to rewrite the Code yet again...

The status' may change, i just thought it was worth mentioning that I would need to be able to change the status'.

**jaslake** · 01-27-2014, 07:06 PM

Hi Cboggie

I need to see it...what do you have...what do you want it to be...show me the input...show me your expected output and how you arrived at it.

Only you can define this...if you can't, it can't be done (not by me).

**Cboggie** · 01-27-2014, 08:02 PM

Ok, I updated the sheet with better explanations.

Hope that helps.vlookup3.xlsm

**jaslake** · 01-29-2014, 05:43 PM

Hi Cboggie

Post a worksheet that looks like this...I'll not build the Sample Data

The data for this calculation will be pasted in from another sheet
The data will always start on A29, so if the calculation could start there
it would be helpful.

**Cboggie** · 01-29-2014, 10:56 PM

jaslake,

appreciate your help very much. Hopefully I've got it right this time.

Here is the sheet

**jaslake** · 01-29-2014, 11:19 PM

Hi Cboggie

I'll look at this tomorrow...late here.

**jaslake** · 01-30-2014, 10:07 AM

Hi Cboggie

Based on your Sample File what totals do you come up with?

For the first calculation I get 1439.76 with a count of 21...for the second I get 1364.88 (for the first 21 records with no duplicates, the count is 20)...what say you?

**Cboggie** · 01-31-2014, 02:14 PM

Originally Posted by jaslake

Hi Cboggie

Based on your Sample File what totals do you come up with?

For the first calculation I get 1439.76 with a count of 21...for the second I get 1364.88 (for the first 21 records with no duplicates, the count is 20)...what say you?

I get 1439.76 > 21 rows for the first calculation

I got 1433.58 > 21 rows for the second

I updated with text and markups of how i got it.

**jaslake** · 01-31-2014, 06:10 PM

Hi Cboggie

Try the Code in the attached...

**Cboggie** · 01-31-2014, 06:42 PM

Yessir!!! That'll do!
Gonna integrate it into the bigger sheet and get back with ya.

**jaslake** · 01-31-2014, 08:08 PM

Hi Cboggie

You're welcome...glad I could help. Thanks for the Rep.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

VBA Vlookup with calculation - no duplicates

LinkBack

Thread Tools

Rate This Thread

Display

VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Re: VBA Vlookup with calculation - no duplicates

Thread Information

Users Browsing this Thread

Similar Threads

VLOOKUP for duplicates

Vlookup With Duplicates

[SOLVED] vlookup duplicates

Vlookup with duplicates

Vlookup with duplicates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions