Forum Home
Press F1
Thread ID: 56793	2005-04-15 07:31:00	Excel sort/order problem...	Mike (15)	Press F1

Post ID	Timestamp	Content	User
345152	2005-04-15 07:31:00	Hopefully I can explain my problem clear enough in here... I have an excel file with 900+ records (rows) with 5 or 6 values per record (columns). Each record has a unique number like "9846 1325". This number is split into two cells, so it looks something like Cell A1= 9846 1325 Cell B1= 9846 Cell C1= 1325 Now what I need to do is sort the whole sheet so that the record in row 2 starts with the last four numbers of the record in row 1 (ie row 2 = "1325 xxxx"), and so on. However (and this is a part that I don't know how it can be sorted out...) while column B values are unique, values in column C aren't necessarily unique... there could be as many as 3 or 4 instances. Does this make sense? In order to explain what I'm trying to sort here, I'll try to explain the concept behind the records :) Imagine drawing a straight line between two points. The point you start drawing has a value (column B), and the point you finish drawing has a value (column C). You then draw another line between two points, starting at the point you just ended the last line, so that value becomes the start value of the next line. More than one line can end at a certain point, but each point can only start one line. I need all the lines to be in order (of sorts...) from start to finish. Any takers? I imagine that this will only using a macro, or more likely some kind of vb script. I am at a loss as to how to create the macro to do it, and I have no vb knowledge. This is for work, and I have our resident Excel guru working on it, but thought I'd ask here as well... if I hear back from the work guru first, I'll post the response here. Cheers, Mike.	Mike (15)
345153	2005-04-15 10:12:00	Cell A1= 9846 1325 Cell B1= 9846 Cell C1= 1325 Now what I need to do is sort the whole sheet so that the record in row 2 starts with the last four numbers of the record in row 1 (ie row 2 = "1325 xxxx"), and so on. Mike. What will happen when you don't have a column A value to match the previous column B value?	wotz (335)
345154	2005-04-15 10:19:00	What will happen when you don't have a column A value to match the previous column B value?Well I'm guessing that we start again with a new start point. Somehow I need to pick a start point that doesn't have any record before it, and go from there until we hit the end, then find another startpoint, and keep going until all records are used (somewhat) sequentially. Mike.	Mike (15)
345155	2005-04-15 10:25:00	Past my abilities, sorry.	wotz (335)
345156	2005-04-15 10:33:00	Past my abilities, sorry.:lol: It was way past mine the moment I volunteered for this "easy" job :p - it sounded easy when I said I'd do it... and then I thought about it ;) Mike.	Mike (15)
345157	2005-04-16 02:13:00	Its unclear what your asking. At first it seems a simple sort by column C then B but perhaps not. To make it clear give an example of say 5 rows as they stand now and the end result you require.	Parry (5696)
345158	2005-04-16 02:44:00	My understanding is that it goes like this A1 x B1 y A2=B1 (y) B2 z A3=B2 (z) B3 w A4=B3 (w) B4 v If there are no rows with v in col A, then A5 t	wotz (335)
345159	2005-04-16 02:59:00	Its unclear what your asking. At first it seems a simple sort by column C then B but perhaps not. To make it clear give an example of say 5 rows as they stand now and the end result you require. My understanding is that it goes like this A1 x B1 y A2=B1 (y) B2 z A3=B2 (z) B3 w A4=B3 (w) B4 v If there are no rows with v in col A, then A5 tThat sounds a bit like it wotz. I don't have my file here, but I'll try to give some useable values as an example (if I can get the right layout in here). COLUMN ACOLUMN BCOLUMN CCOLUMN DCOLUMN E9451 54329451543215060010273 952210273952222522516871 42851687142851505251385 167461385167466006009522 199009522199003004005432 13855432138560052516746 168711674616871525525 would become something like COLUMN ACOLUMN BCOLUMN CCOLUMN DCOLUMN E9451 5432945154321506005432 1385543213856005251385 1674613851674660060016746 16871167461687152552516871 428516871428515052510273 95221027395222252259522 19900952219900300400Basically that's it, except for the fact that sometimes Column C will have two records that have the same number... I don't know how this can be dealt with at this stage - perhaps it all needs to be sorted backwards (I can't quite get my head around that bit). Mike.	Mike (15)
345160	2005-04-16 03:06:00	Well that didn't work... It's a pity I can't insert tables into here... I'll try again :) COLUMN A | COLUMN B | COLUMN C | COLUMN D | COLUMN E 9451 5432 | 9451 | 5432 | 150 | 600 10273 9522 | 10273 | 9522 | 225 | 225 16871 4285 | 1687 | 14285 | 150 | 525 1385 16746 | 1385 | 16746 | 600 | 600 9522 19900 | 9522 | 19900 | 300 | 400 5432 1385 | 5432 | 1385 | 600 | 525 16746 16871 | 16746 | 16871 | 525 | 525 would become COLUMN A | COLUMN B | COLUMN C | COLUMN D | COLUMN E 9451 5432 | 9451 | 5432 | 150 | 600 5432 1385 | 5432 | 1385 | 600 | 525 1385 16746 | 1385 | 16746 | 600 | 600 16746 16871 | 16746 | 16871 | 525 | 525 16871 4285 | 16871 | 4285 | 150 | 525 10273 9522 | 10273 | 9522 | 225 | 225 9522 19900 | 9522 | 19900 | 300 | 400 Basically that's it, except for the fact that sometimes Column C will have two records that have the same number... I don't know how this can be dealt with at this stage - perhaps it all needs to be sorted backwards (I can't quite get my head around that bit). The values in Column D & Column E are irrelevant to this exercise - they're just there to indicate that there are other values in each row. I hope this works now ;) Mike. PS I don't know why there are extra lines in the second example - they're not intentional.	Mike (15)
345161	2005-04-16 04:57:00	Finally it's clear. I think. ;) I suppose the easiest way to handle the records "gone to" more than once would be to add a "Used" flag field, so after each point had been visited once, it wouldn't be used again. My approach would be to get a file and write a programme in Pascal. But I like writing code. I do have a suspicion that a spreadsheet might not be the best tool. :)	Graham L (2)
1 2 3