How to perform a range of functions only for when the values of column A are identical

[laurenf92]

Hi everyone,

I'm working on a huge spreadsheet where I basically have to consolidate a lot of info. The spreadsheet contains info about the grants applied for by employees -- I've mocked up a simple example of what I'm dealing with and am attaching it here. Each row represents a different grant; the columns are the employee that applied for it, its proposed amount, its received amount (if this grant has been funded), the grant's status (Funded, Declined, Pending, or Withdrawn).

The goal is to consolidate this info so that each row is a unique employee's name, with the info summarized in columns for each employee (TOTAL amount proposed by that employee, TOTAL amount received, total # of grants applied for, total # funded, total # declined, etc etc etc).

For the last couple hours I've been doing this manually -- creating a new row for each employee, summing the total amounts proposed, summing the total amounts received, counting the total number of grants, counting the number of cells that read "Funding", then deleting all the other rows for that employee (the rows listing each individual grant)...

Is there a way to automate this process, so that Excel will count the # of Status cells that say "Funded," "Declined," etc., and sum the total proposed and received amounts, for each employee -- that is, for each group of rows that contains the same unique Employee name in Column A?

I would so appreciate any help!!

Please downloaded Example2 to see the structure of the data I'm working with vs. what I want:
Example2.xlsx

[JasperD]

Try this - paste it in a module. It assumes that your rows start row 2, row 1 being a header and that the sheet with your date is in "Sheet1"

Please Login or Register  to view this content.

Worked fine on your example sets

[laurenf92]

Hi JasperD,

I'm new to VBA, so I'm sorry if this is a silly question, but when I run it it just creates a new sheet with the header, and nothing else? Am I doing something wrong?

[JasperD]

Hi Lauren,

your sheet with data is probably not called 'sheet1'. Change this line to have the correct sheet name.
Please note that the macro expects the data to be exactly like your sample sheet - so names in column A etc.

Please Login or Register  to view this content.

[laurenf92]

Hi JasperD,

Thanks so much for replying. My sheet was called "Sheet1," and everything is formatted exactly the same. I actually tried to run that macro in the example file I attached here and it did the same thing -- just made a new sheet with only the header filled in...any ideas?

[JasperD]

Try the attached file. Works for me on Win7 / Excel 2013
Example.xlsm

[laurenf92]

I ran it on a Windows computer and it worked PERFECTLY! Thank you sooooo much! This is a life saver!

There's another feature I'd like to add if it's not too hard to do. There's a column called Departments, and some employees have more than 1, which are listed in different rows for the same employee. Would there be a way to have Excel keep the department name for an employee if it's the same department name all the way down for that unique employee, but if there are multiple unique values for "Department" for the same employee, to have each new unique value put into the next column (labeled Second dept, Third dept, and so on)? And then to highlight the rows for each employee who has multiple departments? I'm attaching another visual example.

Thanks so much for all your help!!!

ExampleWithDepartments attached:
ExampleWithDepartments.xlsx

[JasperD]

Please Login or Register  to view this content.

[laurenf92]

Thank you, thank you, thank you! You're a hero!