Help to reorganise data in a table please

[jab40]

Hi -- could anyone help me?

I have a table that essentially looks like this:

A B C
App # Spons 1. Spons 2.

1 App1 Bob Joe
2 App2 Joe Susan
3 App3 Susan Bob
4 App4 Bob Joe
5 App5 Bob Susan

And I want to get the data into this format:

A B C D E
Name 1 2 3 4

1 Bob: App1 App3 App4 App5
2 Joe App1 App2 App4
3 Susan App2 App3 App5

Is there a way I can set up a spreadsheet with a set of formulae to basically search the first spreadsheet and return the information in the format shown in the second?

Hope this makes sense, and thanks for your help.

**Edit: Okay this didn't seem to make sense as I had hoped. Basically the first spreadsheet lists all the applications in the first column, and the two sponsors of each in the second and third column. I want to have a spreadsheet with each of the sponsors' names in the first column, and the applications they are sponsoring in the next columns.

 

[hiker95]

jab40,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D
1	App #	Spons 1.	Spons 2.
2	App1	Bob	Joe
3	App2	Joe	Susan
4	App3	Susan	Bob
5	App4	Bob	Joe
6	App5	Bob	Susan
7
Sheet1

After the macro in new worksheets:

Excel Workbook
	A	B	C	D	E
1	App1	App3	App4	App5
2
Bob

Excel Workbook
	A	B	C	D
1	App1	App2	App4
2
Joe

Excel Workbook
	A	B	C	D
1	App2	App3	App5
2
Susan

If you ran the macro again your would get in worksheet Bob:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	App1	App3	App4	App5	App1	App3	App4	App5
2
Bob

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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 06/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=565903
Dim w1 As Worksheet, ws As Worksheet
Dim LR As Long, LR2 As Long, a As Long, aa As Long, LC As Long, wLC As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To LR Step 1
  For aa = 2 To LC Step 1
    If Not Evaluate("ISREF(" & w1.Cells(a, aa) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = w1.Cells(a, aa).Value
    Set ws = Worksheets(w1.Cells(a, aa).Value)
    wLC = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    If wLC = 1 And ws.Cells(1, 1) = "" Then
      wLC = 1
    Else
      wLC = wLC + 1
    End If
    ws.Cells(1, wLC).Value = w1.Cells(a, 1).Value
  Next aa
Next a
w1.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.