Sum-ifs VBA

[rockchalk33]

Hey all,

So I am stuck on quite a large project. I have a sheet with a list on names in column A...the rest of the columns (B through I) are numerical values associated with that name.

Here's the rub...there could be only the one one, two, or even up to twelve different rows all containing the same name but different numerical values in the respective columns.

I need to a subroutine to add up all the values in columns "B" that have the same name in column A, columns "C" that have the same name in columns A, repeating this process through column "I"....while simultaneously deleting the row after the summation has occurred.

I can't seem to figure this one out at all and can't find any answers online. PS the data will already be in alphabetical/numerical order

Thanks for the help!!!

Example:

Devin 1 2 3 5 7
Jorden 2 2 2 2 2
Jorden 3 4 7 7 2
Jorden 0 1 1 1 2
Jorden 1 1 2 3 4
Lydia 5 7 9 0 3
Lydia 1 1 1 6 9
Phil 2 7 9 1 0

Would ultimately look like this (without any spaces between rows):

Devin 1 2 3 5 7
Jorden 6 8 13 13 10
Lydia 6 8 10 6 12
Phil 2 7 9 1 0

 

[FDibbins]

Why not just use a regular formula, off to the side/in another sheet, in a small table. That way your base data is still there, and you can always go back and check where a number comes from?

 

[rockchalk33]

Why not just use a regular formula, off to the side/in another sheet, in a small table. That way your base data is still there, and you can always go back and check where a number comes from?

Wish I could do that however I have a variable set of data each day with an unknown number of unique names.

 

[hiker95]

I have a sheet with a list on names in column A...the rest of the columns (B through I) are numerical values associated with that name.

rockchalk33,

So that we can get it right on the first try, can we have an actual screenshot of the raw data, and, the results?


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[rockchalk33]

rockchalk33,

So that we can get it right on the first try, can we have an actual screenshot of the raw data, and, the results?


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

I actually left the office sometime after I had posted this thread. If the solution has not been found, once I return tomorrow I will post screenshots of the data. The example I had posted in the original post is pretty close to what I'm looking for. I just need it to be variable independent and not hard-coded.

I'm posting this on a mobile device so perhaps my example looks different via viewing on a PC. The names in the example are representing what is in column A, then a space representing what is in column B, then a space representing what is in column C, etc... Below that example is what the ultimate goal is and is structured in the same fashion.

Thanks

 

[FDibbins]

Wish I could do that however I have a variable set of data each day with an unknown number of unique names.

These can be extracted by formula, too

 

[rockchalk33]

These can be extracted by formula, too

Can you post an example, I guess I'm a little confused how to do so.

 

[FDibbins]

Something like this...

	L​	M​	N​
2​	Wednesday		Wednesday
3​	Thursday		Thursday
4​	Friday		Friday
5​	Saturday		Saturday
6​	Sunday		Sunday
7​	Monday		Monday
8​	Tuesday		Tuesday
9​	Wednesday
10​	Thursday

N2=IFERROR(INDEX($L$2:$L$10,MATCH(0,INDEX(COUNTIF($N$1:N1,$L$2:$L$10),),0)),"")
copied down

 

[rockchalk33]

rockchalk33,

So that we can get it right on the first try, can we have an actual screenshot of the raw data, and, the results?


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

I have finally figured out how to extract unique values from a specific column, below is an image of what I need help with now. All the unique truck names are listed in column A and starting in column R is the full table array. What I need is for cell B2 to total all the miles for every truck with the truck name "1", B3 to total all the miles for every truck with truck name "3", etc....This cannot be hardcoded however and must be set for any arbitrary amount of unique values.

Thanks!

PS this must be done in VBA, Thanks

https://postimg.org/image/frxq2dy79/

 

[hiker95]

rockchalk33,

Your link has additional images?????


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com