Complex search and filtering/sorting of data - formula needed

[emilyshaw93]

Hi there,

I have a very complex workbook spread over 2 sheets and need some help searching and sorting through my data using a formula (not a macro).

I have a sheet called 'Initial List' with lots of names in one column (column F) and the document numbers that that legislation is referred to in it (column D). Some of the names in that list are repeated, as they are in multiple documents.

I then have another sheet in this workbook with a sorted list (ie with duplicates removed) of the list of names. This is called 'Sorted List'.

What I need is a formula (I think a complex IF formula is needed) that looks at the Initial List sheet to see if the name in that sheet matches the name in 'Sorted List' sheet, and if it does, to pull over into one cell (Concatenate?) all of the document number references to that name in the 'Initial List' sheet. See my tables below for explanation.

'INITIAL LIST' SHEET
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column D (Doc no.)[/TD]
[TD]Column F (List of names)[/TD]
[/TR]
[TR]
[TD]1F[/TD]
[TD]Emily[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]8C[/TD]
[TD]Emily[/TD]
[/TR]
</tbody>[/TABLE]

'SORTED LIST' SHEET
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column B (Sorted Names)[/TD]
[TD]Column I (Doc nos. referred to)[/TD]
[/TR]
[TR]
[TD]Emily[/TD]
[TD]1F, 8C[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5J[/TD]
[/TR]
</tbody>[/TABLE]

Any help much appreciated

 

[emilyshaw93]

PS: The name match needs to be exact!

 

[Peter_SSs]

If I have understood your question correctly, such a result using standard Excel formulas would only be feasible if the number of rows of data in 'Initial List' is very small. eg No more than 10 or 20 rows. It seems like you may have many more than that ".. sheet called 'Initial List' with lots of names .."

If that is the case then you really need to consider allowing a vba approach.