#1
|
|||
|
|||
Creating a merged list with added numbers
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. Last edited by Dbruhns; 07-22-2014 at 09:15 PM. Reason: Easier to read |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Macro Help
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? |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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 End With This will give you a basic sorted listing, though you'll have ostensibly separate sets for 'F & M' and 'M & F'.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |