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
345162	2005-04-16 07:33:00	Ok, heres my initial attempt at this. It all depends upon how the data is sorted because Im not sure whats supposed to happen if the value your looking for cannot be located. If it cant find the value from column C in column B then I am formatting the column c value yellow. Im assuming you want to do this all the way down to the last used cell in column A Sub Mike() Dim i As Long, c As Range For i = 1 To Range("A65536").End(xlUp).Row - 1 On Error Resume Next Set c = Range("B:B").Find(what:=Cells(i, 3).Value, After:=Cells(1, 2), LookIn:=xlValues) If Err.Number <> 0 Or c Is Nothing Then Cells(i, 3).Interior.Color = vbYellow GoTo Skip End If On Error GoTo 0 c.EntireRow.Cut On Error Resume Next Cells(i + 1, 3).EntireRow.Insert shift:=xlShiftDown On Error GoTo 0 Skip: Next i Application.CutCopyMode = False End Sub	Parry (5696)
345163	2005-04-16 09:56:00	Ok, heres my initial attempt at this.Thanks Parry - looks good :) Now for the long wait until Monday so I can try it out on the real thing. It does seem to work on my little example, and a couple of modifications. 'til Monday, Mike.	Mike (15)
345164	2005-04-17 07:05:00	Your problem is intriguing. I’m thinking about it and still trying to get my head round it. It would seem to be related to navigation: a fascinating puzzle. What causes the generation of all these ‘co-ordinates’, and what for? I too am convinced a spreadsheet is not the best tool. Another little voice says there may be another way of coming at the basic problem. Is it possible to step back past the point where you’re generating those tables and take a fresh look at the whole approach? I shall continue to watch this thread with interest. I have you placed roughly at the end of Hairini Street – is that about right?	Mike S (1766)
345165	2005-04-17 09:34:00	I have you placed roughly at the end of Hairini Street – is that about right?Well done - how'd you figure that out? I'll try to reply to the rest of your post after Top Gear has finished in about 20 minutes :) Mike.	Mike (15)
345166	2005-04-17 10:42:00	Your problem is intriguing. I’m thinking about it and still trying to get my head round it. It would seem to be related to navigation: a fascinating puzzle. What causes the generation of all these ‘co-ordinates’, and what for? I too am convinced a spreadsheet is not the best tool. Another little voice says there may be another way of coming at the basic problem. Is it possible to step back past the point where you’re generating those tables and take a fresh look at the whole approach? I shall continue to watch this thread with interest. I have you placed roughly at the end of Hairini Street – is that about right?I don't believe it! I just spent ages typing out an explanation of what I'm doing, hit Submit, and it says that I'm not logged in!!! :badpc: So I start again... I'll try to explain the basics of what I'm doing :) We've had a survey of manholes done for a certain area of the city. Each manhole has a unique manhole ID, and these IDs are transferred across to pipes that attach to each manhole. Each pipe has two IDs attached to it - the ID from the upstream manhole and the ID from the downstream manhole. These are the values I need to sort my data on. Basically what I'm wanting to get from the spreadsheet is a sequential list of pipes from the first manhole in each line of manholes to where it hits another line (and so merges with that line I suppose). This sequential list will the enable me to identify anomolies in other data attached to the pipes (such as pipe diameters etc.). Does this make sense? The data has been supplied in CSV format, so it is in spreadsheet form for me for starters, and the analysis once I can get the data sorted will be carried out using Excel. However if another process could be used to sort them then I'm happy to try it :) It is possible that I could get them sorted using some spatial technique, but that's a bit beyond me at this stage as well, and I doubt I'd get much help from the PressF1 folk (unless there are a couple of GIS geeks hiding around the place that I don't know of?). I'm keen to find a way to sort the data no matter what process is used. Not as clear and simple as my first attempt at this post, but I couldn't remember everything I typed the first time :( Cheers, Mike.	Mike (15)
345167	2005-04-19 08:41:00	So did you try the code then?	Parry (5696)
345168	2005-04-19 21:39:00	So did you try the code then?Didn't quite work... will try to explain what happened tonight :) basically when there were duplicates in column C, already sorted values were re-sorted, so it left gaps... I'll see if I can find somewhere to put my full XLS for you to look at. Thanks, Mike.	Mike (15)
345169	2005-04-20 02:26:00	mm seems odd that you have duplicates when its described as a chain. I mentioned it all depends upon how the data is organised so I guess it falls into that bucket. :-) Send me a PM if you like and you can send the book to me. regards, Graham	Parry (5696)
345170	2005-04-20 02:43:00	Graham (Parry): I suggested a way to handle that in my previous posting: just add a tag field to each record. Test and set that field when you get to the record. If it was set, leave the record where it is. It's an end of chain. I think. ;) I'd want to draw a few pictures from real data to get real feel for it. I had thoughts of linked lists and pointers but after sketching a type definition with clever things, I decided that an array of arrays of integers would probably do it. Pascal would eat it. ;)	Graham L (2)
345171	2005-04-20 05:24:00	Graham (Parry): I suggested a way to handle that in my previous posting: just add a tag field to each record. Test and set that field when you get to the record. If it was set, leave the record where it is. It's an end of chain. I think. ;) I'd want to draw a few pictures from real data to get real feel for it. I had thoughts of linked lists and pointers but after sketching a type definition with clever things, I decided that an array of arrays of integers would probably do it. Pascal would eat it. ;) Hi Graham. Yup I could but too early to say what approach to take yet. I could simply place the row # in an array then query the array each time etc but may be a waste of time if the chain is supposed to have multiple points which branch off thus each number may have multiple offshoots but this all depends upon what logic is used to decide where it starts/ends.	Parry (5696)
1 2 3