Your original sample file had phone numbers in every 4 columns, your recent sample file has them all over the place.
So I've used a new method to determine whether a column is phone number or not; In row 1 (the headers) when a cell contains the word
Phone and does not contain the words
Seen or
Type then it's deemed a phone number. This is handled by the first part of the formula:
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type"},B$1)))="100"
There are probably better ways of doing that.
The second part of the formula is much the same as before but handles dates and numbers better.
The whole formula in cell B40 of the attached is:
Code:
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type"},B$1)))="100",IF(ISERROR(MATCH(B1,$A1:A1,0)),IF(B1="","",B1),""),IF(B1="","",B1))
copied down and across. Column A is a simpler formula.
This may be solvable in different ways and it depends on your version of Excel;
do you have the LET & LAMBDA functions available to you on the sheet?
What is your version of Excel?
It's likely a Power Query solution will be better.
Where are you getting the data from? Are you pasting it into Excel from elsewhere? If so it's very likely you'll be able to get that data into Excel, transform it, then present it all in the background, using Power Query.
If you answer the underlined questions above, and perhaps state what you're ultimately trying to do we could have a slicker and a more robust solution for you.