Apply function on specific rows, based on criteria

[El_engineer]

Hi,

I would like to apply some functions on SPECIFIC rows of a sheet. Imagine you want to run through 500 rows of another sheet and apply
a function ONLY for the rows where e.g. Location=UK AND Year=2012. From these rows get the content of a third column (e.g. person)
and concatenate or sum or min or whatever. I got a little confused with ifs and ranges and I don't know if vlookup can help.

Thanks

[:) Sixthsense :)]

Check Sumifs…

if possible attach a sample workbook with enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

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

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[El_engineer]

I uploaded the excel now.

You can see here what I want. But sumif will work somehow for other functions?

[:) Sixthsense :)]

Wow nice picture

Seems good for my eyes but excel is not allowing me to work with it

[El_engineer]

You don't need to! It's just to get the idea, how do you get the concatenated result from the person column ONLY for
rows where Location and Year match, and not from all of them?

[:) Sixthsense :)]

Without a sample file I can't test and suggest the right solution.

[El_engineer]

Here you are.

[:) Sixthsense :)]

Whether duplicate matches cannot be shown in the result?

Because for UK 2011 the matching results are John;George;Jim;Greg;Greg;John
But you have shown the expected result as John;George;Jim;Greg

In which you have shown the john only once.

Whether the duplicate data should not be shown?

[El_engineer]

I want to show DISTINCT values from the concatenate.
That means :
John;George;Jim;Greg for UK, 2011. 4 and not 6 names.

Thanks

[:) Sixthsense :)]

Add the below code in Standard Module of your workbook…

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

Clookups Range Usage

=Clookups(LookupValue1, LookupRange1, LookupValue2, LookupRange2,ResultRange,DelimitorText)

In F3 Cell

=Clookups(A3,Sheet2!$A$3:$A$15,B3,Sheet2!$B$3:$B$15,Sheet2!$C$3:$C$15,";")

Drag it down…

[El_engineer]

That was really good! I am starting to use Macros but I haven't gone that far. Seems that you can do
a lot of things with custom functions. Thank you so much!