Pivot Table? Index Match & Concatenate Across Sheets?

[mymeeshell]

Sample file attached but here's the overview:

I have a sheet with a list of target contacts in each row and the company they work for in a column. Also in columns are the names of the people I know who can introduce me. I need to keep the data in this sheet structured with just one target contact per line so I can make notes when I talk to them.

I want to email my friends to ask them to intro me to these targets. But I only want to send one email to each friend, asking for intros to all my targets that that friend knows. So I want to create another sheet where each friend is a unique row (challenge #1 is to get friends who may appear in multiple columns on sheet 1 into unique rows in sheet 2). Then each friend corresponds to the targets they can intro me to (challenge #2 is to pull corresponding targets from previous sheet, match them with their corresponding friend (who may appear in any of four columns)), so I want those targets in columns next to each friend. Finally, I want the the columns that include the targets to also include the company they work at, so this column would be "Target Name, Company Name"

Or if there's a way to do this with a Pivot Table, that'd be great to know too.

Thanks in advance!!

 

[Tiago MF]

Hi

Please see the file attached and validate.

I've added a helper column on sheet "Target Contacts" to combine the target's name and company for simplicity's sake.

Also, I've defined names for certain ranges which need to be adapted if you add new contacts (to counter that you can insert rows above for new contacts instead of adding them below).

BR,

 

[mymeeshell]

Thanks Tiago! When I tried to apply this to my actual data, I got some funky results. I understand the helper column but am struggling to understand how it's pulling the Person I Know column and then the Who They Can Intro Me To columns.

Could you walk me thru how those work (I'm not understanding how Large works here, and also, why include the -1? in
=IFERROR(INDEX(Contacts,LARGE(IF(Friends=$A3,ROW(Friends)-1),COLUMNS($A:A))),"")?

Also when I double click in these cells to see / copy the formula, then click enter without changing anything, I either get a #VALUE! error or the cell turns blank when previously it had pulled data.

Again, thanks for your help!

 

[Tiago MF]

Hi

The formula for the "Person I Know" column isn't very easy to understand/explain, but it's the only way I know to extract unique data from a table (multiple rows and columns) range.

Firstly please note that the basis of the formula is the INDIRECT function which in this case is fed by functions that will return R1C1 style references. To better understand it you should make sure you're familiar with INDIRECT.

Note how the indirect is basically composed of two parts (highlighted in red and blue below):
=INDIRECT("'Target Contacts'!"&TEXT(MIN(IF((Friends<>"")*(COUNTIF($A$1:A1,Friends)=0),ROW(INDIRECT("$2:$"&ROWS(Friends)+1))*100+COLUMN('Target Contacts'!$D:$G),7^8)),"R0C00"),)&""

"'Target Contacts'!" - this is to make reference to sheet "Target Contacts", where our data is

TEXT( .... ,"R0C00") - this uses the Text function to mold values into R1C1 style references and let us extract the unique contacts from range D2:G11 of Target Contacts sheet. So for example, on our first result this part of the formula will return R2C04 (row #2, column #4, i.e. cell D2, which on the Target Contacts' sheet is "Becky"). Now, the first argument of Text is a Min with an If inside it. It's the combination of the If with Min that will assure that as you drag down the formula the results will be unique. So the argument of the If is:
(Friends<>"")*(COUNTIF($A$1:A1,Friends)=0)

*note that Friends is a named range for the reference D2:G11

This means that for cells within that range that are either blank (="") or which value is already listed on above results, the argument will be FALSE and the If will return 7^8*, which is just some big number to make sure the MIN will always pick the values that you truly want.

*this number when fed into the Text will reference cell $A$57648, so you shouldn't have any value on this cell. For the eventuality that you are working with a huge table, make sure to use some other big number instead of 7^8, just note that if you don't choose a number that ends in 01 you'll have to incorporate an IFERROR( ... , "") just so that the results are clean (i.e. as you keep dragging the formula down it doesn't show #REF or some other error results after all unique values have been extracted).

Since this post is already too big, I'll let you explore the remaining part of the formula, i.e. what the IF returns when the argument is TRUE:
ROW(INDIRECT("$2:$"&ROWS(Friends)+1))*100+COLUMN('Target Contacts'!$D:$G),7^8))
But if you don't understand it just ask and I'll explain it on another post.

I can also later explain how the formula for the Who They Can Intro Me To columns works, but I'll let you digest all of this first. But try to break down the formula to see what each part results into so that you better understand how they work altogether. Just make sure you read about INDIRECT first if you aren't familiar with it yet.

Finally...

Also when I double click in these cells to see / copy the formula, then click enter without changing anything, I either get a #VALUE! error or the cell turns blank when previously it had pulled data.

It's because it's an Array formula. Array formulas need to always be entered by pressing the Control, Shift and Enter keys altogether and not just Enter or else they don't work. Notice how the formula contains this characters { } delimiting it; this characters are to indicate that it has been entered as an array formula. I suggest you to read some posts chandoo has made about array formulas