Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-22-2014, 09:05 PM
Dbruhns Dbruhns is offline Creating a merged list with added numbers Windows 7 64bit Creating a merged list with added numbers Office 2010 64bit
Novice
Creating a merged list with added numbers
 
Join Date: Jul 2014
Posts: 4
Dbruhns is on a distinguished road
Send a message via Yahoo to Dbruhns
Default 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
Reply With Quote
  #2  
Old 07-23-2014, 05:15 AM
macropod's Avatar
macropod macropod is offline Creating a merged list with added numbers Windows 7 32bit Creating a merged list with added numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
  #3  
Old 07-23-2014, 02:25 PM
Dbruhns Dbruhns is offline Creating a merged list with added numbers Windows 7 64bit Creating a merged list with added numbers Office 2010 64bit
Novice
Creating a merged list with added numbers
 
Join Date: Jul 2014
Posts: 4
Dbruhns is on a distinguished road
Send a message via Yahoo to Dbruhns
Default 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?
Reply With Quote
  #4  
Old 07-23-2014, 03:27 PM
macropod's Avatar
macropod macropod is offline Creating a merged list with added numbers Windows 7 32bit Creating a merged list with added numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 07-24-2014, 10:30 AM
Dbruhns Dbruhns is offline Creating a merged list with added numbers Windows 7 64bit Creating a merged list with added numbers Office 2010 64bit
Novice
Creating a merged list with added numbers
 
Join Date: Jul 2014
Posts: 4
Dbruhns is on a distinguished road
Send a message via Yahoo to Dbruhns
Default

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
Reply With Quote
  #6  
Old 07-24-2014, 03:08 PM
macropod's Avatar
macropod macropod is offline Creating a merged list with added numbers Windows 7 32bit Creating a merged list with added numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
  #7  
Old 07-25-2014, 08:01 AM
Dbruhns Dbruhns is offline Creating a merged list with added numbers Windows 7 64bit Creating a merged list with added numbers Office 2010 64bit
Novice
Creating a merged list with added numbers
 
Join Date: Jul 2014
Posts: 4
Dbruhns is on a distinguished road
Send a message via Yahoo to Dbruhns
Default

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.
Reply With Quote
  #8  
Old 07-25-2014, 04:48 PM
macropod's Avatar
macropod macropod is offline Creating a merged list with added numbers Windows 7 32bit Creating a merged list with added numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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'.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

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
Creating a merged list with added numbers Align Mail Merged Numbers to the Decimal Point HangMan Mail Merge 1 12-11-2013 02:32 PM
Creating a merged list with added numbers decimal numbers not appearing properly in merged document paulys Mail Merge 2 08-06-2012 08:15 AM
Creating a merged list with added numbers Creating TOC with letters and numbers tanababa Word 1 04-28-2011 01:35 AM
Creating a merged list with added numbers creating unique numbers in excel bignick270 Excel 1 05-17-2009 05:40 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:01 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft