Converting a database

**TomH89** · 12-12-2012, 09:25 AM

Using an existing database with nonfixed boundaries I am creating a new one with uniform boundaries.
The simple spreadsheet I will use in this comment isn't what the database actually looks like but represents what I am trying to achieve.

Start Finish Assigned #
0 15 1
15 25 2
25 40 3
40 55 2
55 60 2
60 80 4
80 95 3
95 100 2

Here I have the old data, organised into sections that have the same assigned number. (Note: the assigned number may be the same as the previous cell). I wish to change it into this structure:

Start Finish #
0 20
20 40
40 60
60 80
80 100

It will be organised by the start and finish instead of by the assigned number. Instead I would like the number to reflect the lowest assigned number that occupies a part of that area.

Is it possible to create a forumla which would achieve this? Although it seems like a simple problem I'm unable to find an answer. Any help would be much appreciated.

**ChemistB** · 12-12-2012, 10:13 AM

I'm not following. Can you explain your logic a bit more? Probably part of the problem is the loss of formatting in the forum. You could attach a spreadsheet (go advanced>manage attachments) or separate the columns with "|" to make it clearer.

**TomH89** · 12-12-2012, 10:25 AM

Agreed, it looked a lot neater as I was writing it! I'll attach the spreadsheet and give some more information.

If you look at the attached doc you can see that I have 'Start' and 'Finish' figures, I want these to be uniform rather than random but rather than having to manually enter the new 'Assigned #' I want to use the existing data. Obviously there is a lot of cross over, where there is I would like to use the lowest 'Assigned #'.

If that's still unclear then please let me know.

**ChemistB** · 12-12-2012, 10:43 AM

So you want each row broken into even units of 20 (in your example) and then an assignment number? How is the assignment number chosen? Is it the lowest number that starts in that range or the lowest number that intersects that range? Is each assignment only used once? It would help if you filled in Col G with what you expect.

Assignment #2 goes from 15 up to 100, wouldn't you put assignment 2 into all rows except row 1 (which would have #1)?

Edit: Ahh, I see #2 doesn't cover the 60-80 range.

**TomH89** · 12-12-2012, 11:03 AM

The assigned number demonstrates importance, the lower the assigned number (1 being lowest) the more important it is and no matter it's size if it appears within a row it always dominates. An assinged number is not only used once, in the actual database each will appear a few thousand times. A useful visualisation may be to consider it as a database for road usage, with the assigned number demostrating traffic usage and the start and finish showing miles.

I've attached the spreadsheet showing how it ought to look (if completed manually).

**ChemistB** · 12-12-2012, 02:45 PM

Just checking on the second row. In the original database configuration, you have #1 assigned only to 0-15. Wouldn't 20-40 be assigned 2?

**daddylonglegs** · 12-12-2012, 05:15 PM

I would agree with that....

Try this "array formula" in G2

=MIN(LOOKUP(ROW(INDIRECT("1:"&F2-E2))+E2-1,A$2:C$9))

confirmed with CTRL+SHIFT+ENTER and copied down

assumes positive integer values......

**TomH89** · 12-13-2012, 08:12 AM

Sorry, you're both correct. The dangers of manual inputting! Cheers daddylonglegs, that seems to do the trick, I've just got to adapt it now. I knew it wouldn't be too hard but was just one of those times when my brain was working against me.