Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • September 30, 2016, 06:53:33 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

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

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,251
    • View Profile
    • Donate to Member
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?

Screenshot - 2014-01-31 , 10_28_22_ver002.pngSOLVED: 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
Tom
« Last Edit: January 31, 2014, 04:55:11 PM by tomos »

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,751
    • View Profile
    • Donate to Member
Re: One of these thing is not like the other - Finding Unique Numbers
« Reply #1 on: January 31, 2014, 05:21:25 AM »
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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,251
    • View Profile
    • Donate to Member
Re: One of these thing is not like the other - Finding Unique Numbers
« Reply #2 on: January 31, 2014, 05:44:16 AM »
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?
(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)
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,251
    • View Profile
    • Donate to Member
Re: One of these thing is not like the other - Finding Unique Numbers
« Reply #3 on: January 31, 2014, 06:27:45 AM »
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).

Quote from: http://www.softmaker.com/forum/viewtopic.php?p=46105#p46105
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).
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,251
    • View Profile
    • Donate to Member
Re: One of these thing is not like the other - Finding Unique Numbers
« Reply #4 on: January 31, 2014, 07:15:19 AM »
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:

Screenshot - 2014-01-31 , 14_12_46.pngSOLVED: One of these thing is not like the other - Finding Unique Numbers

Tom

rjbull

  • Charter Member
  • Joined in 2005
  • ***
  • default avatar
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: One of these thing is not like the other - Finding Unique Numbers
« Reply #5 on: January 31, 2014, 04:35:25 PM »
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)
You might try a port of Unix comm, which I mentioned in DONE: Are any of the files missing???

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,251
    • View Profile
    • Donate to Member
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)
You might try a port of Unix comm, which I mentioned in DONE: Are any of the files missing???

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
Tom

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,751
    • View Profile
    • Donate to Member
Re: SOLVED: One of these thing is not like the other - Finding Unique Numbers
« Reply #7 on: February 01, 2014, 04:15:13 AM »
I hope I havent put you to too much trouble on this one Ath, and thanks for the offer :Thmbsup:
NP, I was just investigating/interested, hadn't started working on anything yet ;)