Unique values from rows combined into single row

[kravspelunker]

Hey all, I apologize if this is an easy topic that I've missed elsewhere in the forums but I haven't had much luck in finding anything.

I have a large spreadsheet that has lots of information that repeats various information in different rows. What I would like to do is to collect all of this information from multiple rows in the spreadsheet into a single row whose cells contain all of the information from all other rows but without any of the repeating. I'm having some difficulty even articulating what I need help with so I've put a quick example of what I am trying to accomplish below. I appreciate your help and responses. Please let me know if I have provided sufficient information.
Many thanks!

Current example:

Row1 Row2 Row3 Row4
machineA unique_value1 FacilityA AppA
machineB unique_value2 FacilityB AppB
machineC unique_value3 FacilityC AppA
machineA unique_value1 FacilityC AppB
machineA unique_value1 FacilityC AppC

Desired result:

Row1 Row2 Row3 Row4
machineA unique_value1 FacilityA, FacilityC AppA, AppC, AppB
machineB unique_value2 FacilityB ApplicationB
machineC unique_value3 FacilityC ApplicationA

 

[hiker95]

kravspelunker,

It is difficult to determine by your post where your raw data actually is?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[kravspelunker]

Excel Workbook
	A	B	C	D
1	Current Data:
2
3	Machine	IP	Application	Facility
4
5	MachineA	xxx.yyy.100.1	App_A	Facility_1
6	MachineB	xxx.yyy.100.2	App_C	Facility_2
7	MachineC	xxx.yyy.100.3	App_B	Facility_3
8	MachineA	xxx.yyy.100.1	App_B	Facility_2
9	MachineA	xxx.yyy.100.1	App_B	Facility_1
10	MachineD	xxx.yyy.100.4	App_A	Facility_3
11
12
13	Desired Result:
14
15	Machine	IP	Application	Facility
16
17	MachineA	xxx.yyy.100.1	App_A, App_B	Facility_1, Facility_2
18	MachineB	xxx.yyy.100.2	App_C	Facility_2
19	MachineC	xxx.yyy.100.3	App_B	Facility_3
20	MachineD	xxx.yyy.100.4	App_A	Facility_3
Sheet3

Excel 2007

 

[kravspelunker]

Hiker95, I think this is what you were asking me for. Thanks for the heads up and please let me know if this is sufficient.
Many thanks!

 

[kravspelunker]

Oh, and to answer your question, it's Excel 2007

 

[hiker95]

kravspelunker,

I assume that your actual raw data titles are in row 1?

 

[snoopyhr]

Not exactly what you are looking for but perhaps very close, this as close as I could come up with, Again I know this is not quite in the format you wanted.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	*	*	*	*	*	*	*	*	*	*	*	*
2	1	Current Data:	*	*	*	*	*	*	*	*	*	*
3	2	*	*	*	*	*	*	Unique Machines	IP-Address	Facilities	*	*
4	3	Machine	IP	Application	Facility	*	*	MachineA	xxx.yyy.100.1	App_A,Facility_1	App_B,Facility_2	App_B,Facility_1
5	4	*	*	*	*	*	*	MachineB	xxx.yyy.100.2	App_C,Facility_2	*	*
6	5	MachineA	xxx.yyy.100.1	App_A	Facility_1	*	*	MachineC	xxx.yyy.100.3	App_B,Facility_3	*	*
7	6	MachineB	xxx.yyy.100.2	App_C	Facility_2	*	*	MachineD	xxx.yyy.100.4	App_A,Facility_3	*	*
8	7	MachineC	xxx.yyy.100.3	App_B	Facility_3	*	*	*	*	*	*	*
9	8	MachineA	xxx.yyy.100.1	App_B	Facility_2	*	*	*	*	*	*	*
10	9	MachineA	xxx.yyy.100.1	App_B	Facility_1	*	*	*	*	*	*	*
11	10	MachineD	xxx.yyy.100.4	App_A	Facility_3	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*	*
Sheet2

 

[hiker95]

kravspelunker,

I assume that your actual raw data titles are in row 1.


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Machine	IP	Application	Facility
2
3	MachineA	xxx.yyy.100.1	App_A	Facility_1
4	MachineB	xxx.yyy.100.2	App_C	Facility_2
5	MachineC	xxx.yyy.100.3	App_B	Facility_3
6	MachineA	xxx.yyy.100.1	App_B	Facility_2
7	MachineA	xxx.yyy.100.1	App_B	Facility_1
8	MachineD	xxx.yyy.100.4	App_A	Facility_3
9
Sheet3

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Machine	IP	Application	Facility			Machine	IP	Application	Facility
2
3	MachineA	xxx.yyy.100.1	App_A	Facility_1			MachineA	xxx.yyy.100.1	App_A, App_B, App_B	Facility_1, Facility_1, Facility_2
4	MachineA	xxx.yyy.100.1	App_B	Facility_1			MachineB	xxx.yyy.100.2	App_C	Facility_2
5	MachineA	xxx.yyy.100.1	App_B	Facility_2			MachineC	xxx.yyy.100.3	App_B	Facility_3
6	MachineB	xxx.yyy.100.2	App_C	Facility_2			MachineD	xxx.yyy.100.4	App_A	Facility_3
7	MachineC	xxx.yyy.100.3	App_B	Facility_3
8	MachineD	xxx.yyy.100.4	App_A	Facility_3
9
Sheet3

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, 07/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=563514
Dim LR As Long, a As Long, SR As Long, ER As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Sort
  With .SortFields
    .Clear
    .Add Key:=Range("A3:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Add Key:=Range("B3:B" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Add Key:=Range("C3:C" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Add Key:=Range("D3:D" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  End With
  .SetRange Range("A3:D" & LR)
  .Apply
End With
With Range("E3:E" & LR)
  .FormulaR1C1 = "=RC[-4]&RC[-3]"
  .Value = .Value
End With
Columns("A:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G1"), Unique:=True
Range("J1:K1").Value = Range("C1:D1").Value
LR = Cells(Rows.Count, 7).End(xlUp).Row
With Range("I3:I" & LR)
  .FormulaR1C1 = "=RC[-2]&RC[-1]"
  .Value = .Value
End With
For a = 3 To LR Step 1
  SR = Application.Match(Cells(a, 9), Columns(5), 0)
  ER = Application.Match(Cells(a, 9), Columns(5), 1)
  If SR = ER Then
    Range("J" & a).Value = Range("C" & SR).Value
    Range("K" & a).Value = Range("D" & SR).Value
  Else
    Range("J" & a).Value = Join(Application.Transpose(Range("C" & SR & ":C" & ER)), ", ")
    Range("K" & a).Value = Join(Application.Transpose(Range("D" & SR & ":D" & ER)), ", ")
  End If
Next a
Columns(5).ClearContents
Columns(9).Delete
Columns("G:J").AutoFit
Application.ScreenUpdating = True
End Sub

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 ReorgData macro.

 

[kravspelunker]

snoopyhr, I have been unable to decipher what exactly the formulas are doing, even when stepping through them, so that I could expand them to account for my whole spreadsheet. When plugged in to the small sample that they cover, however, the results are most helpful. How do I expand the scope of what these formulas are looking at? I know it sounds like a dumb question (and most likely since I've asked it, it is) but every time I try to change them to look at a larger area, I get no data back.

hiker95, the macro also summarizes all of the data that I am after but the cells have tons of repeat information when I plug it in. (for example, cell c3 might have "application_A, application_A, Application_A, Application_B, Application_C". Do you have any suggestion on what I might do to eliminate the repeats?

Both of you, thank you so much for taking a look at my problem and helping me out.

 

[hiker95]

kravspelunker,

Are you looking to eliminate duplicates in individual cells in columns I and J?


I3 displayes:
App_A, App_B, App_B

You are looing for?
App_A, App_B


J3 displayes:
Facility_1, Facility_1, Facility_2

You are looing for?
Facility_1, Facility_2