1. ## 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

2. ## Re: From/To Range (XP)

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

3. ## 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

4. ## 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

5. ## 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.

Hans,

Thanks,
John

7. ## 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. ## 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
•