Returning multiple results match with no duplicates

LionGeo

New Member
Joined
Jul 22, 2019
Messages
6
Hi all,

New here and would really like some help with a formula.

(Table created for clarity) I would need the formula to firstly find the persons name in the first Row, once it has been found it would look along the row and return a specific cell (For this instance say its the second row), it would then in the next cell to the returned information do the search again but ignore the previous returned result and return a different one.

Say we wanted to know the result from the third row for Katie for all of her accounts, the first returned result in the cell would be 'Open 1st', then to the right of that result it would return the next one 'Closed 5th', then lastly 'Open 2nd', and so forth until it has returned all results.

The only sort of duplicates I dont want returned is when its from the same cell, if the same text appears on a different cell then that is fine.

Katie20/06/1984Open 1st
John06/02/1978Open 2nd
Peter31/01/1991Closed 1st
Katie20/06/1984Closed 5th
Suzie15/12/1988Open 1st
Katie20/06/1984Open 2nd

<tbody>
</tbody>

Thanks in advance for the help if its possible, it would be a massive help for what is needed to be done with over 20000 accounts.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi & welcome to MrExcel.
How about


Book1
ABCDEFGH
1
2Katie20/06/1984Open 1stKatieOpen 1stClosed 5thOpen 2nd
3John06/02/1978Open 2ndJohnOpen 2nd
4Peter31/01/1991Closed 1stPeterClosed 1st
5Katie20/06/1984Closed 5thSuzieOpen 1st
6Suzie15/12/1988Open 1st
7Katie20/06/1984Open 2nd
Sheet1
Cell Formulas
RangeFormula
F2=IFERROR(INDEX($C$2:$C$7,AGGREGATE(15,6,(ROW($C$2:$C$7)-ROW($C$2)+1)/($A$2:$A$7=$E2),COLUMNS($A$1:A$1))),"")
 
Upvote 0
Hi & welcome to MrExcel.
How about

ABCDEFGH
1
2Katie20/06/1984Open 1stKatieOpen 1stClosed 5thOpen 2nd
3John06/02/1978Open 2ndJohnOpen 2nd
4Peter31/01/1991Closed 1stPeterClosed 1st
5Katie20/06/1984Closed 5thSuzieOpen 1st
6Suzie15/12/1988Open 1st
7Katie20/06/1984Open 2nd

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=IFERROR(INDEX($C$2:$C$7,AGGREGATE(15,6,(ROW($C$2:$C$7)-ROW($C$2)+1)/($A$2:$A$7=$E2),COLUMNS($A$1:A$1))),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Hi and thanks for the response and welcome!

Tried the formula above and it did not work, does it make any difference if I noted its Excel 2016?
 
Upvote 0
As I dont know how to edit posts I wanted to add/Clarify.

I have a specific list of names/reference numbers, and I need to get the results for those names/references. So I would need it to lookup the reference number to locate it within the first column.
 
Upvote 0
That's exactly what the formula does.
Please define "did not work"
 
Upvote 0
That's exactly what the formula does.
Please define "did not work"

Apologies, It is just showing up blank.

When I change the reference being matched to the same as the first reference being checked then it works perfectly, anyway else and its blank.
 
Upvote 0
What is the formula you are using & what are the ranges?
 
Upvote 0
=IFERROR(INDEX($C$2:$C$15000,AGGREGATE(15,6,(ROW($C$2:$C$15000)-ROW($C$2)+1)/($A$2:$A$15000=$E2),COLUMNS($A$1:A$1))),"")

Same Formula just increasing the ranges the for the amount of data
 
Upvote 0
In that case if its returning "" it sounds like the value in E2 cannot be found in col A
Check that you don't have any leading/trailing spaces.
 
Upvote 0
Thanks for all the help, it finally worked. Sorting out the data to be ascending got it to click together.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top