Formula to move specific information from columns and rows to a single row.

Please help. I have a worksheet that identifies our membership information on one row. The row title has the following data located within each column: A) Member ID; B) Membership name; C) Street Address; D) City; E) State; F) Zip; G) Contact Name; H) Contact Category; and I) Email address.

My problem is that in Column H there are four categories listed for each member but stacked in the same column. i.e. Main Contact (H, row 3), Loss Control Contact (H, row 4), and Claims Contact (H, row 5). But my problem is that I want to move the data in Column G, H and I, rows 3, 4 and 5 respectively, all to one row. (See Current Worksheet). So with over 600 members, I don’t want to be clipping and pasting for the next two months.

What I need is a worksheet that will have the following information for each member on a single row: A) Member ID; B) Member Name; C) Billing Contact Name, D) Billing Contact Email; E) Main Contact Name; F) Main Contact Email; G) Loss control Name; H) Loss Control Email; I) Claims Contact Name; J) Claims Contact Email. See Outcome Worksheet).
Any assistance would greatly be appreciated.

Membership List.V1.xlsx

Hi,

See the outcome table, I have added formula from column B and so on. All you need to do is copy and paste your membership ID from original sheet and paste it to outcome sheet.

Note that Membership ID has to be unique, otherwise the formula would show incorrect result.

Cheers
Albert


Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Thank you very much. Your solution worked for me.