transpose related data from row into columns

I have a Sign up sheet for an event we are having for our community centre In column A we have the family names and the members participating from the family in Column B
I am trying to put the family members (transpose) in separate columns and only have the family names on each row to have each row for all Mother participants, Son participants etc etc.
can this be done/?

Lisa4legin,

Detach/open workbook GetFamilyMembers oarray - Lisa4legin - EF967186 - SDG15.xlsm and run the GetFamilyMembers macro.

With your raw data beginning in cells:
A3 = "Family A"
B3 = "Father"

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetFamilyMembers macro.

I have used WS functions.

Im still try for the better result.

Please try this file.

Another one. Output in sheet 3.

Lisa4legin,

You are very welcome. Glad we could help.

Thanks for the feedback.

And, come back anytime.