Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    From/To Range (XP)

    I have a list of accounts that are mapped to another system.

    What I would like to do is produce a From/To range assigned to the mapping column (please see example).

    Any suggestions would be appreciated.

    Thanks,
    John
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: From/To Range (XP)

    Could you attach your sample workbook? That makes it easier for me and other Loungers to experiment.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: From/To Range (XP)

    If you know you don't have overlap, you can use a pivot table. Group on Mapping in the Row field area and use Min and Max functions to get the from and to ranges. Note that you will need to convert your numeric fields to actual numbers for the Min and Max to work.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: From/To Range (XP)

    Hans,

    I have attached a sample workbook and have shortened the account list.

    The account list that I have is quite long and I am guessing VBA code would be the solution but I don't know where to start.

    Thanks,
    John
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: From/To Range (XP)

    A pivot table as suggested by Rory is the simplest solution. Here is a macro:

    Sub CreateFromTo()
    Dim r As Long
    Dim m As Long
    Dim t As Long
    m = Cells(Rows.Count, "B").End(xlUp).Row
    ' Row before start of range to be filled
    t = 1
    For r = 2 To m
    If Not Cells(r, "C") = Cells(r - 1, "C") Then
    t = t + 1
    Cells(t, "E") = Cells(r, "B")
    Cells(t - 1, "F") = Cells(r - 1, "B")
    Cells(t, "G") = Cells(r, "C")
    End If
    Next r
    ' Last one
    Cells(t, "F") = Cells(r - 1, "B")
    End Sub

    You'll have to modify it to suit your real worksheet.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: From/To Range (XP)

    Hans,

    Your suggestion worked.

    Thanks,
    John

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: From/To Range (XP)

    Hans,

    As I previously stated your code worked just fine. In flipping it around, how would one assign a mapping to an account falling within a From/To range?

    Thanks,
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: From/To Range (XP)

    You can use VLOOKUP for this. Let's say that you have an account in cell H11 (it must be a text value, since your accounts are also text values). With the table as in your sample workbook, the formula

    =VLOOKUP(H11,E2:G7,3)

    will return the corresponding mapping.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •