VBA version of this front-of-house formula to make workbook less heavy

RoseG

New Member
Joined
Dec 14, 2010
Messages
14
Hey all,

Could anyone possibly advise if there is some VBA version of the following formula, that I could use. My workbook is all slow and sluggish because this thing is so bluberous.

=IF(AND(B4<>"",(SUMPRODUCT(--($B$4:B4=B4),(--($D$4:D4=D4)),(--($S$4:S4=S4))))<=1,(SUMPRODUCT(--(B4:$B$2000=B4),(--(D4:$D$2000=D4)),(--(S4:$S$2000=S4))))>1),SUMPRODUCT((B4:$B$2000=B4)*(D4:$D$2000=D4)*(S4:$S$2000=S4),(AF4:$AF$2000)),"")

Key information:
Column B is a date
Column D is a name
Column S is a location
Column AF is an ammount of time

I want to count up the ammount of time that the same person went to the same place on the same day and enter this info in column AN, on the first row of this set.

What I want Excel to do is:

Look in Columns B, D and S to see if there are duplicated entries e.g.
B1=14/04/2011, D1=John Wayne and S1=South Library
B2=15/04/2011, D2=John Wayne and S2=South Library
B3=14/04/2011, D3=John Wayne and S3=South Library

Row 1 and 3 are a match. No other matches.

Then I want it to add up the total ammount of time from column AF and enter this in column AN on the first row of the matching rows.
In the example given, this would be row 1. No entry to appear in rows 2 or 3.

Oh and I don't want Excel giving me any of its lip if no info has been entered yet. Hence the condition of B<>"" in the formula.

Is there anyone who can help?

Rose xxxx
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You would almost certainly be better off adding a new key column that concatenates columns B, D and S, and you can then use SUMIF/COUNTIF which will be a lot more efficient.
 

RoseG

New Member
Joined
Dec 14, 2010
Messages
14
Top banana!

No more sluggish worksheet with this new improved fromula:

=IF(AND(COUNTIF(AN13:$AN$2000,AN13)>1,COUNTIF($AN$4:AN13,AN13)=1),SUMIF(AN13:$AN$2000,AN13,AF13:$AF$2000),"")

Might even slim it down with a SUMPRODUCT if I can figure out how.

Thanks so much!
 

RoseG

New Member
Joined
Dec 14, 2010
Messages
14
Hey all,

Sorry to bug with an old issue but, now I've filled in the rest of my sheet, the Concatenate solution is still slowing the book down something chronic.

Any further suggestions? I'd really appreciate it.

Thanks muchly!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,444
Messages
5,528,799
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top