[ms office - o365] Excel Query - Can anyone help please

[thedoctor]

Hi All,

 

I have recently downloaded student data, including contact details. Trouble is their are multiple columns because they have different priority contacts.

 

Example

Adno Name Reg Year Parental Salutation Defined Field Relationship Priority Parental responsibility Defined Field_2 Full name Title Primary Telephone Primary Email Mobile number

999999 Bloggs, Jane 8L 8 Mr & Mrs Bloggs Mother 1 T Mrs Bloggs Mrs 1 [email protected] 1

999999 Bloggs, Jane 8L 8 Mr & Mrs Bloggs Father 2 T Mr Bloggs Mr 2 [email protected] 2

999999 Bloggs, Jane 8L 8 Mr & Mrs Bloggs Aunt 3 F Mrs Potter Mrs 3 [email protected] 3

 

 

 

What I am trying to produce is

 

Name - Contact 1 - Contact 2 - Contact 3 - Contact 4 as an example on one single column.

 

Can anyone please help? It would be very much appreciated.

 

Many Thanks

 

J

Example Data.xlsx

[Bedders]

This should help. https://www.automateexcel.com/formulas/vlookup-duplicate-values/

 

Basically add a column to your source Student data that creates a unique ID, then you can VLOOKUP each of the contacts with it if there's a matching "Name-Number" combination. You could throw a fancy IF statement in there too, to allow for blanks.

 

Example Data WithAmendments.xlsx