Author
Topic: SOLVED: One of these thing is not like the other - Finding Unique Numbers (Read 2276 times)

First, I dont have excel - I use Softmaker Office/Planmaker and Libre Office. I can get access to Excel though if the solution lies there.

Summary:I'm wondering if it's possible -- using any type of software --to find numbers in a spreadsheet that are NOT shown in the first column?

SOLVED: One of these thing is not like the other - Finding Unique Numbers

In the screenshot, '674', in cell C131, does not exist in the first column.

The first column should contain all the numbers shown elsewhere -- but I'm not 100% certain it does.So, if there is a number elsewhere in the sheet which is not duplicated in column one, I need to knowthe number, and also to know which column it is in (FWIW, it's location within the column is not important). This is (possibly) complicated by the fact that a number could exist twice in one column -- but still not be in first column; or a number could be shown in multiple columns, except for first (the latter is unlikely and should not be the case).

I guess I could create a text file from each column, and compare somehow (dont have anything installed for that so if this the course to take, pleaase recommend something)

1) There's the 'Default' column and seven six others (one was almost the same as the 'Default' so I was able to rule that out 'manually')

2) current file format is .xls (created with Planmaker/Softmaker)

3) Output doesnt matter - there wont be many (there might not even be any). I just need to know from which column the number came (column indicates a particular file with related info). Even if I can only get a screenshot of the results, I'll be happy ;-)

Edit// FWIW, I may have to do this again at a later stage (if I get more info from a different source)

This can be done via conditional formatting.In the attached sample document, columns B and C got a red background as standard format.Then, all cells in columns B and C got conditional formatting using the formula =MATCH(B1;$A$1:$A$36;0)>=1, which renders the background transparent if the condition is fulfilled, i. e. the number in the cell matches with any number in column A.(NB: This is the original formula for cell B1; it was then transferred to the other cells using "Transfer cell formatting".)Now, if in columns B or C any cell contains a number existing in column A, the conditional formatting makes the background transparent. If the number in a cell is not present in column A, conditional formatting doesn't work and the original red background is visible (as in cell C11).

After a bit of messing around - I got a simpler variation on that one working using conditional formatting with formula. I found quite a few that had been missed (the info should all have been transferred already to the 'default' file, i.e. column A).

I hope I havent put you to too much trouble on this one Ath, and thanks for the offer

SOLVED: One of these thing is not like the other - Finding Unique Numbers

thanks rj - I just changed the thread title to reflect that I got a solution (as quoted in reply #3). Not sure if this would work the same in other spreadsheet programmes, but using Planmaker:

apply this formula below in conditional formatting dialogue to cell B2 (B1 is column header)=MATCH(B2;$A$1:$A$xxx;0)>=1where xxx is the number of rows in column A that you want to compare with the rest e.g 400.Apply some sort of highlighting formatting in the dialogueyou then transfer that formatting to all other cells that you want to compare with column A. This automatically adjusts the formula (you can tell I'm a real spreadsheet noob here lol)Everthing that is duplicated is highlighted - so my numbers were the ones that werent highlighted