Hahahaha, love them to bits, users, that is.
I’ve got a spreadsheet containing data representing proposed changes to a customer database… actually, four different customer databases on four different systems (don’t even ask why the database is split up like that, it’s a terrible legacy thing).
Aaaaanyway, the person who prepared the spreadsheet containing the data has helpfully marked each row as belonging to one of the four databases by… colouring it. I need to find a way to sort by colour so that I can split it back into four blocks of data. There are reasons (including duplication of data etc) that I can’t just relate the whole list to each individual database - it would result in lots of erroneous changes. There are also reasons (hierarchical) why I can’t just throw the spreadsheet back in the face of the guy who prepared it.
Soooo… Is there any way to sort rows in an Excel spreadsheet by their formatting? (or are there any format-aware-expressions I could incorporate in a formula in an extra column?)
Is there any chance that you could create a field that references the source of the data so that you have a 1:1 relationship between the data in that field and the color of those rows? Then you could sort by that field.
Wow, that’s tricky.
OK, the best I can come up with is that you can copy / paste all the cells into Word. In Word you can do format based search / replace (Edit / Replace / More button). So you can search for all red coloured text and append the word RED to the start of the line. Now take that and copy / paste it back to Excel and you can use the standard string searching to do your work.
The easiest way, I guess, is to insert a new column into the spreadsheet that has the same formating per line but has an block of text that is the same that you can search for. Then send to Word, search and replace that text with something sortable by Excel. Then send back to Excel and sort on that column.
A quick test here shows that should work, although I’m not sure how best to fill in the new column easily (Copy Down nukes the formatting).
Preview reveals that LostCause probably has a better solution but I’ll post my round-the-houses hack anyway.
SD
Okay, this is easy – don’t let the number of steps scare you off.
[ul]
[li]In your Excel file, choose Tools->Macro->Visual Basic Editor.[/li][li]The VB editor will start.[/li][li]Now select Insert->Module.[/li][li]You’ll have a blank editing area, and the first line of VB code will already be there, and probably be “Option Explicit.”[/li][li]Below that text, paste (having copied) the following text:[/li]
Function GetMyColor(theRange As Range) As Long
GetMyColor = theRange.Interior.ColorIndex
End Function
[li]Okay, just close the VB window; you don’t need it.[/li][/ul]
Now the “GetMyColor” function is just a normal Excel function. Here’s how you can sort by color:
[ul]
[li]Say all of your color-coded things are in column A, and that column B is available.[/li][li]Just put GetMyColor(A1) into B1. You can copy and paste or fill-down column B to put the same function in every other cell in the column (yes, you probably already know this; it’s for future posterity).[/li][li]Now you’ll have just a bunch of numbers representing the color codes of the cells.[/li][li]You can now sort on this column.[/li][/ul]
Thanks for the suggestions folks; as it happens, they were pushing me hard to apply the data, so I turned this into an argument as to why someone would have to sit and manually mark the rows (it was only a couple of thousand of them, ). I’ll definitely make a note of these solutions though, as this has come up before and will no doubt come up again.
For the record, no, there’s no way to uniquely identify the data just by looking at it, except the colour - it was originally four separate spreadsheets (one for each system), containing exactly the same columns, containing exactly the same kind of data - this is because a decision was made long ago (that causes me all kinds of trouble, believe me) to expand a single-currency sales system to multiple currencies by simply running several copies of it in parallel :eek:
Over the years though, some companies have moved from one currency to another (chiefly when the Euro was introduced) and so their details have been copied from one place to the other, keeping the account reference intact, however some of the account number ranges overlap anyway, for different customers in different currencies :smack: It’s a f***ing nightmare!
Balthisar’s method is great, so a big shout-out to him/her. I’ve been wondering how to do this for years.
A couple of things to note – I had to type out “Option Explicit” at the head of the VB editing area. Also, for what it’s worth, I hit save before exiting that module. Also, make sure to preface the GetMyColor function, when you invoke it, with a “=”. That is, when you type the function in a cell, type =GetMyColor(A2) – which true Excel-heads know they have to do anyway.
One final observation – this method works only for colored cell backgrounds, not for colored text. But I’m sure Balthisar can show us how to do that as well!
Balthisar = he.
For colored text, it looks like changining “Interior” to “Font” would do the trick.
FWIW, I don’t actually know all of this stuff. I just know how to figure it out in a hurry.
Honestly, that’s way more impressive. Being able to solve new computer issues is vastly superior to knowing how to solve old computer isssues.