1

My programming skills are next to none so I’m looking for some help or to be pointed in the direction that I would be able to infer and build my own. This is for Excel.

I am trying to take data from one sheet and if the value for a name matches, have it select a row two to the right and then input that to another sheet.

Example

Garrett 1:20

Stephanie 1:15

Garrett 1:25

Garrett 1:10

So I need something where I can input a name, so that I can use the same code or similar code for each person.

So if I want to input a name have it search mine, Garrett, then take the times two rows over and input those times into another sheet or average them in the code if that is possible.

Let me know if I’m unclear or if this is pointless.

8 Spice ups
2

Maybe it’s because I haven’t had my cup of coffee yet this morning, but I am having a bit of a hard time duplicating your requirements.

So on sheet 2 you input a name (e.g. Garrett). then the VBA code digs through sheet 1, finds all instances of Garrett, copies the times (from ColB) over to Sheet 2 and computes and average.

Correct?

3

I follow you.

Easiet way I see:

Sheet 1: Input text box and button for name and to run macro.

Starting at cell say 5 have your headers for the data it is going to capture, here i.e. name and time.

Then have the macro output the data it finds in these columns.

Sheet 2: The data you are using. The macro will search this page and return the values into the first sheet

After the loop is done on sheet 2, go back to sheet 1 and average column, say, d.

I have this exact program written. I can upload some code soon.

4

Yep,

I’d like it to be able to specify the name to look for such as mine, Garrett, it then averages every time next to the instances that match my names in the specified ranges.

5

I modified slightly from my current version, but this should be what you’re looking for. It’s messy but it works. IF you need help understanding what it all means, just ask!

Dim sheetCount As Integer

Dim rwIndex As Integer

Dim homeIndex As Integer

homeIndex = 5

Sheetcount=2

Do While sheetCount < 3

For rwIndex = 2 To 1000

If (Worksheets(sheetCount).Cells(rwIndex, 10).Value = Worksheets(1).Cells(1,10 ).Value) Then ’ match names

Sheets(“Sheet” & sheetCount).Activate

Rows(rwIndex & “:” & rwIndex).Select

Selection.Copy

Sheets(Sheet1).Activate

Rows(homeIndex & “:” & homeIndex).Activate

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

homeIndex = homeIndex + 1

Else

End If

Next rwIndex

sheetCount = sheetCount + 1

Loop

6

I don’t mean to ask to much, would you be able to comment it by chance please so I can reference what line does what and what values to change?

Thank you!

7

Surely. All the syntax is there. You would just need to do the averages bit, this collects all the data from one sheet, where value a matches values in a column on another sheet.

8

I think you may be over complicating things. Does it need to be VBA? From what I have read, you can accomplish this using Excel built-in functions. I have included a screenshot showing column A with peoples names, column B with a value for them. Then Column D has one of the names, and Column E does an AverageIF to get the Average of column B, where Column A has the name contained in Column D… (See top for the formula used in column E.)

AverageS.PNG

These are shown on the same page for simplicity, the two tables can easily be stored on two different sheets.

9

Here you are. Hope it helps.

Macro_Instructions.docx (14.5 KB)

10

Doesn’t need to be VBA if there is another way, but how is it specifically averaging instances of the names you specified?

I’m not good at reading excel formulas, so I apologize.

The averaging I know can be done easily, but I’d like the times to be automatically pulled based on the inputted names if you specify A1:A30 all instances of one name will have the time pulled and then averaged if that makes sense.

11

If all you’re trying to do is get averages, you can use the built-in sub-totals command.

  1. Sort your list by name.

  2. Insert a subtotal (from the Menu Ribbon, select Data, then click on the Subtotal button). You formula should read something like, At each change in name Use function Average, Add subtotal to Time

Your result will be something like:

Name	Time
Garrett      	1:20
Garrett      	1:25
Garrett      	1:10
Garrett Average	1:18
Stephanie    	1:15
Stephanie Average	1:15
Grand Average	1:17
12

Do you need the data fields copied, or is the end result goal this:

Garret 1:18
Stephanie 1:15
Grand Average 1:17

If so, what I have provided would work…

=AverageIF(, , )

With the Grand Average just being a normal Average of the entire column in your Data sheet.

13

So the macro would work fine, but they are right with the subtotal function if it is just for one simple sheet. Quick and easy as well, no formulas or code needed.

14

If you want to be able to just type in the name and have the spreadsheet fill itself automatically I would use Excel functions rather than VBA. VBA is much more powerful but dynamically updating is not one of its strong suits…that’s where the built-in excel functions come in much more handy.

Other functions to look into would be VLOOKUP and HLOOKUP…pretty handy for the types of things you’re trying to do.

15

I cannot use a simple average formula, unless there’s another way to do it than I know.

This is because, the cells that contain my name and others names that I will be referencing are not necessarily specified.

For example, someone may have their name input on the 15th, 20th, and the 21st, but that is not set in stone. So I cannot reference specific cells, I would like something that can search a range of cells for a specific name and relate the time that is comparable to the name.

16

The formula I provided does exactly what you are asking.

=AverageIF(, , )

It calculates an average for column B, for rows where column A contains what you are looking for. Am I missing something?

If Col A has the names, and Col B has the times, =AverageIF(A:A,“Garrett”,B:B)

17

If none of these seem to meet your needs, can you provide an example of data so we can see what this is so complicated?

18

Because I asked you above to explain how it worked, as you just did.

So without knowing that, as I said before I am limited in my programming and even my excel skills. So I apologize. I will test both this and Mkarmil’s code tomorrow and see what works.

19

Definitely worked, I very much so appreciate that.

excel.PNGIf I have that, and I only want to average the times that say “No” in the third column, is there a way I can make a formula to search for all instances of Garrett and average the times, but only if it says no in the third column?

That way it does not average the times that say “Yes”.

20

=AVERAGEIF(M1:M23, H1, O1:O23, “No”, B1:B23)

This is what I tried, but it did not work. H1 is referencing a cell with my name in it.