Complicated- find duplicates from multiple columns and return a value

**lmcglory** · 06-09-2014, 11:42 AM

Hello,
I need help figuring out how to do this, or if it's possible.
My spreadsheet has multiple "sessions" by date and each has three columns: a name, their organization, and a column where we want to display an "R" if they are a repeat participant. Each new session is entered to the right of the last. The names are in every third column. Like so:

name company R
name company
name company
name company R

Is it possible to search through the whole document to find repeating names, and then display an "R" in every third column if they are a repeat participant?

Thanks in advance!

**ChemistB** · 06-09-2014, 03:43 PM

Can you upload an example of what you are talking about? (Go Advanced>Manage Attachments)
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

**bionikspoon** · 06-09-2014, 04:20 PM

repeat_customer.xlsx

Hello,

I think I understand what you're looking for--basically if a company shows in the column more then once you want the row to be marked with an "R".

I posted a solution in the attachment using dummy data. To summarize I used 2 vlookups to search for repeats and an IF statement to summarize the results.In the attachment, Col C is the whole formula put together, Col E, F, and G are the 3 formulas separated so it's easier to understand.

**bionikspoon** · 06-09-2014, 04:31 PM

I just realized the way I posted was excessively complicated:

Another way to do it is using "countif" and an "if" statement to summarize it.

For example, using the dummy data I just posted, in row 2 the formula would be

Formula:

Please Login or Register  to view this content.

And you can autofill that down the list. Also, you could take out the IF statement and leave the number to show how many times the company ordered stuff--which is also good info.

**newdoverman** · 06-09-2014, 07:44 PM

I understand the problem to be that a name may appear more than once down the column and that you want an R placed in the row starting at column C and for each repeat after the first appearance an R will be placed in column C then the next in column D etc as the person repeats.

This solution will place the correct R's in the columns as the name is entered in the list in column A. This goes out to column G which would represent an original session and 5 more repeats. If more is required, copy the formula to the right. Copy the formula down as required.

This is the formula to be entered in C2 and copied across and down as required.

Formula:

Please Login or Register  to view this content.

The enclosed file shows the results....I just used letters instead of names (I'm lazy).

The second file has a summary area if that would be of value.