Create a list that sums values of selected names in a dynamic range

[PAexcel]

Hi,

I have a range that I want to be dynamic since I will be inserting and deleting rows in the dataset. Out of this dataset, I would like a formula based solution to create a list of only upper-cased names and sum the values of the numbers associated with the name. In my example below, I am using "-" as column separators.


AA - 10 - BB - 5
Aa - 5 - CC - 15
E1 - 7 - AA - 10
gg - 10 - DD - 5
DD - 5 - E1 - 3


Results:

AA - 20
BB - 5
CC - 15
DD - 10
E1 - 10


Thanks,

PAExcel

[JBeaucaire]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Saarang84]

Assuming that your data is placed in the range A2:D6, try,

Please Login or Register  to view this content.

[PAexcel]

@JBeaucaire

My company will not allow me to add/receive files from forums. I posted a sample of the data and the desired outcome in original post (see above). You can create your own sheet in excel to troubleshoot, then post the correct commands here for me to try. That is the best I can do. Keep in mind that I am looking for a formula based answer, not VBA. It's your choice if you would like to help. thanks

[PAexcel]

Assuming that your data is placed in the range A2:D6, try,

Please Login or Register  to view this content.

When I use this formula, I'm getting one cell that reads a zero. Please see the desired output listed as "results" above. I need a formula that will create a list of only the CAP or CAP and number entries in columns A & C showing duplicates only once and placing the sum of the numbered data found in columns B & D next to the associated identifier from column A & C. Thanks