Creating a merged list with added numbers

Dbruhns · #1 07-22-2014, 09:05 PM

I'm using office 2010 and I have two columns of data.

Column A is a list of names (currently 22, but can fluctuate)
Column B is a score for each name in column A.

I need to create a third column with pairing of each name in Column A. ( without duplication)
And a fourth column with a combined total of the score for each of the names combined.

Example:
Col A..........Col B............Col C...............Col D
John............100.........John & Steve.........250
Steve..........150..........John & Roy............220
Roy.............120..........John & Kirk...........230
Kirk............130..........John & Diane.........210

Diane..........110.........Steve & Roy...........270
................................Steve & Kirk...........280
................................Steve & Diane........260
................................Roy & Kirk.............250
................................Roy & Diane..........230
................................Kirk & Diane..........240

(Decimals used for place keeping to show columns)

Any help in how to do this would be grateful.

macropod · #2 07-23-2014, 05:15 AM

You could do this quite easily with a macro:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, lRow As Long
With ThisWorkbook.Worksheets("Sheet1").UsedRange
  lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
  For i = 1 To lRow
    For j = 1 To lRow
      If i <> j Then
        k = k + 1
        .Range("D" & k).Value = .Range("A" & i).Value & " & " & .Range("A" & j).Value
        .Range("E" & k).Value = .Range("B" & i).Value + .Range("B" & j).Value
      End If
    Next
  Next
End With
Application.ScreenUpdating = True
End Sub

Dbruhns · #3 07-23-2014, 02:25 PM

I had a reply to my initial Post saying it is easy to do what I wanted by creating a macro.

A macro was even written and attached to the reply.

Now my question is how do I insert / associate / execute this macro in my actual excel worksheet?

macropod · #4 07-23-2014, 03:27 PM

To use the macro, press Alt-F11 to open the VBA Editor. On the left, you should see your workbook's name and below that 'ThisWorkbook'. Double-click on 'ThisWorkbook', then paste the macro code into the main panel. You might want to change the macro's name (Demo) to something more meaningful to you. Press Alt-F11 to close the VBA Editor, then activate your worksheet. Finally, press Alt-F8, choose the macro and click OK.

Dbruhns · #5 07-24-2014, 10:30 AM

Thanks Paul,

One more question if you don't mind. The Macro created the listing beautifully.

However, it created a few more pairings then required.

In my original list I have a Pairing of Steve & Roy once I have a pairing of Steve & Roy I do not need nor want a pairing of Roy & Steve.

Is there a line or two that can be inserted in the logic to eliminate this kind of duplicate pairings?

Thank
-Don

macropod · #6 07-24-2014, 03:08 PM

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, lRow As Long
With ThisWorkbook.Worksheets("Sheet1").UsedRange
  lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
  For i = 1 To lRow - 1
    For j = i + 1 To lRow
      k = k + 1
      .Range("D" & k).Value = .Range("A" & i).Value & " & " & .Range("A" & j).Value
      .Range("E" & k).Value = .Range("B" & i).Value + .Range("B" & j).Value
    Next
  Next
End With
Application.ScreenUpdating = True
End Sub

Dbruhns · #7 07-25-2014, 08:01 AM

Once again, Thank you Paul,

I have one last request,

If I inserted a column at Column A that represents gender "M" or "F"

What would be the change necessary to make the pairings be either only "M" with "M" or "F" with "F" or just "M" with "F"?

Since I am inserting a column, I have already determined what needs to be changed in the current Macro in order to read the correct columns for required output data.

Once again this is great information and I thank you very much for your help.

macropod · #8 07-25-2014, 04:48 PM

It would be nice if you said up-front what the requirements are instead of dishing them out piecemeal. Neither I nor anyone else enjoys writing and re-writing code just because the person asking for it left out some vital information.

Without a complete re-write to accommodate a requirement for gender pairings, you could simply add:
.Range("F" & k).Value = .Range("C" & i).Value & " & " & .Range("C" & j).Value
after:
.Range("E" & k).Value = .Range("B" & i).Value + .Range("B" & j).Value
then add:

Code:

  With .Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("F1:F" & k), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("D1:F" & k)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With

before:
End With

This will give you a basic sorted listing, though you'll have ostensibly separate sets for 'F & M' and 'M & F'.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Added page numbers and now printing unwanted blank pages.	galvax	Word	2	12-18-2013 08:41 PM
Align Mail Merged Numbers to the Decimal Point	HangMan	Mail Merge	1	12-11-2013 02:32 PM
decimal numbers not appearing properly in merged document	paulys	Mail Merge	2	08-06-2012 08:15 AM
Creating TOC with letters and numbers	tanababa	Word	1	04-28-2011 01:35 AM
creating unique numbers in excel	bignick270	Excel	1	05-17-2009 05:40 AM