To separate names from numbers and to display them singularly

[BrutalExcel]

Hi!

I have a document where names are generated from the Missing Names tab to the JUNE tab. When the names are displayed, the date which is associated with the name will be indicated red in the JUNE tab. However, there are a few particulars that need to be edited for which i need help with. I have attached the document herewith and need to solve the below issues:

1) When the same name appears on the list but contains a different date, how can i make it occur only for a single line with all those dates instead of multiple lines? You can refer to the names Johny and Frederikson in the document to see what i mean.

2) How can i display only the names into the JUNE tab without the numbers which are included together with the names? Note that the names are auto-generated and have different lengths. The numbers in front of the names can differ too, where they might be longer for some of the names.

It would be so great if someone could help me with this.
Thanks!

Regards,

[Andrew-R]

Well this happy little formula entered in D7 does most of what you're looking for:

=IF(ROW(A1)>SUMPRODUCT(--(ROW($A$7:$A$140)=MATCH($A$7:$A$140,A:A,0)),--($A$7:$A$140<>"")),"",MID(INDEX(A:A,SMALL(IF(ROW($A$7:$A$140)=MATCH($A$7:$A$140,A:A,0),ROW($A$7:$A$140)),ROW(A1))),MATCH(TRUE,INDEX(CODE(MID(INDEX(A:A,SMALL(IF(ROW($A$7:$A$140)=MATCH($A$7:$A$140,A:A,0),ROW($A$7:$A$140)),ROW(A1))),ROW($1:$255),1))>=65,0),0),255))

This is an array formula so MUST be entered with Ctrl-Shift-Enter, not just enter, and I've taken too long working this out, so somebody will have come up with a much simpler way by now

[BrutalExcel]

Hi!

Thanks so much! Pretty long happy formula! This seems to work so far. However, after applying the formula, the cells for the associated dates which were highlighted in red seem to have disappeared. Is there a way that the cells can be highlighted while using your formula?

Thanks!

Regards,

[Andrew-R]

Yes, change the formula used for the conditional formatting to red to:

=COUNTIFS($A$6:$A$140,VLOOKUP("*" & $D7,$A:$A,1,FALSE),$B$6:$B$140,E$4)>0

[BrutalExcel]

Hi Andrew-R,

Thanks so much! It works fine!
Greatly appreciated!

Regards,