Main Area and Open Discussion > General Software Discussion

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

(1/2) > >>

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)
--- End quote ---
(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
--- End quote ---

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 :Thmbsup:

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

Navigation

[0] Message Index

[#] Next page