SEARCH [Nested Function]

[ExcelJunkie]

Hi All:

Without writing any Macro code, I need assistance on how do to the following:

I need a function (or nested function) to SEARCH down the “Person Responsible” column and separate (or summarize) each person's categories and dollar amounts into their own summarized areas with totals.

See two attached images. The first one is without the function "BEFORE". The second is an example of what I would like it to look like after the function does its job - "AFTER."

How would I do this without writing a Macro? Or, to format the cells as shown with headings, borders, colored totals, etc... if I do need to write a Macro, what would the code look like (would prefer to do without a Macro).

The values may change depending on the person who's entering data, so I would need something that is "adaptive" or can change based on user input to create the "BEFORE" type of image.

Notice in the example, how the code searched down the "Person Responsible" column and found duplicates for John, and took all of his categories and dollar amount and created a summary total for him, so that he can be concerned with only what he manages.

Thank you so much!

[Flintstone]

Exceljunkie:


This will only work if the numbers in column C are whole numbers.

Type a name in cell B1, the paste this in column CA4 and copy down as far as needed.

=IF(B4="","",IF(B4=$B$1,C4+(ROW()/100000),""))

Paste this formula in cell CB4 and copy down.

=IF(ISERR(LARGE(CA:CA,ROW(A1))),"",LARGE(CA:CA,ROW(A1)))

Paste this formula in cell E4 and copy down.

=IF(CB4="","",IF(ISNUMBER(CB4),INDIRECT("A"&MID(CB4,FIND(".",CB4),6)*100000),""))

Paste this one in cell F4, copy down.

=IF(CB4="","",IF(ISNUMBER(CB4),INDIRECT("B"&MID(CB4,FIND(".",CB4),6)*100000),""))

Paste this one in cell G4, copy down.

=IF(CB4="","",IF(ISNUMBER(CB4),INDIRECT("C"&MID(CB4,FIND(".",CB4),6)*100000),""))


Matt

[ExcelJunkie]

Hi Flinstone:

Thank you so much for taking the time, but nothing working? In terms of "whole numbers", the C column was formatted to "Accounting", but I changed to "Number", and to "General", and still no output.

Tried several different things. The E, F, and G columns are blank. Not output of any kind. What am I missing? Any other methods or ideas on how to accomplish this?

Thank you so much!

[Flintstone]

Hello ExcelJunkie:


I’ve simplified the formulas a bit. Replace formula in column CA with this.

=IF(OR(B4="",B4<>$B$1),"",ROW())

Replace formula columns E, F, and G with these respectively.

=IF(OR(CB4="",$B$1=""),"",INDIRECT("A"&CB4))
=IF(OR(CB4="",$B$1=""),"",INDIRECT("B"&CB4))
=IF(OR(CB4="",$B$1=""),"",INDIRECT("C"&CB4))

And just to clarify:
1. Type John’s name in cell B1.

2. If you copied and pasted the formulas off the web make sure to remove the unwanted spaces.

Other than that I have no idea why it doesn’t work for you; it does for me.
Maybe it's the INDIRECT() function?


Matt