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

[tomos]

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 know the 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)

TIA, Tom

[Ath]

A few questions upfront:

• How to determine the number of columns?
• What's the standard file-format, Excel (xls, xlsx), CSV, LibreOffice (odf)?
• What's the desired output? Messagebox, console, file: format?

(More to follow, probably, depending on the answers )

[tomos]

A few questions upfront:

• 1) How to determine the number of columns?
• 2) What's the standard file-format, Excel (xls, xlsx), CSV, LibreOffice (odf)?
• 3) What's the desired output? Messagebox, console, file: format?

-Ath (January 31, 2014, 05:21 AM)

(I numbered them)

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)

[tomos]

I should have said I also posted in the Softmaker forum and just got an answer using conditional formatting.
I'll have to study it a bit (I'm a very basic spreadsheet user).

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).

-http://www.softmaker.com/forum/viewtopic.php?p=46105#p46105

[tomos]

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

[rjbull]

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)

-tomos (January 31, 2014, 03:48 AM)

You might try a port of Unix comm, which I mentioned in DONE: Are any of the files missing???

[tomos]

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)

-tomos (January 31, 2014, 03:48 AM)

You might try a port of Unix comm, which I mentioned in DONE: Are any of the files missing???

-rjbull (January 31, 2014, 04:35 PM)

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)>=1
where 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 dialogue
you 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

Resulting in the image in my previous post

[Ath]

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

-tomos (January 31, 2014, 07:15 AM)

NP, I was just investigating/interested, hadn't started working on anything yet