Excxel Question...

Electronics Computer Programming Q&A
Post Reply
User avatar
Lenp
Posts: 1518
Joined: Thu Oct 26, 2006 8:11 pm
Location: Maryland
Contact:

Excxel Question...

Post by Lenp »

I have two 'identical' spreadsheets that are supposed to have their formula, layout and format the same, so they should act the same!
One sheet is older than the other, and the newer sheet is doing something wrong that the older sheet is not doing.

It's expected to return the current time to it's cell if several conditions are matched, but it returns nothing, and it's the same in all the similar cells, so the error seems to have been replicated through the sheet. Finding the error(s) in the non-functioning sheet is like looking for a needle in a haystack of about 2,000 cells!

So, what I am looking for a way to compare a FORMULA'S SYNTAX in two different cells that should be exactly the same. The inspected cells could have the same result even if there was an error in one cell's formula. What I've found only compares the cell's results.

Example 1:
Sheet1 cells A1, A2 and A3 all contain the value of 10. The formula =if(A1=A2),"T","F") would return a "T" since the results match.

Example 2:
Sheet 2 cell A1, A2 and A3 all contain the value of 10. The formula =if(A1=A3),"T","F") would return a "T" since their results match
even though their formulas are using different cell references .

Using 'plain' English... If the syntax, not the results, in Sheet 1 cell A1, and Sheet 2 cell A1 are a match, let me know, If and ignore the cell's values.

If I find a workable formula, I can put Sheet1 on TAB1, Sheet 2 on TAB 2 , and the compare the two sheet's cells on TAB 3.....maybe!
Len

“To invent, you need a good imagination and a big pile of junk.” (T. Edison)
"I must be on the way to success since I already have the junk". (Me)
User avatar
CeaSaR
Posts: 1875
Joined: Sat Nov 08, 2003 1:01 am
Location: Phoenixville, PA USA
Contact:

Re: Excxel Question...

Post by CeaSaR »

Hmmm, without seeing them personally, I'd revert to the old DOS "FC" command.

Easiest way I know is to make a folder C:\Temp, copy each file into that (renaming in the process to a short name like old.xls and new.xls) and then open up a command prompt. Then type:

Code: Select all

CD C:\Temp <ENTER>
FC old.xls new.xls <ENTER>
Any differences in the files will be explained. It has been a long time since I've had to do that, so I can't recall exactly how it shows the differences, but I remember it does.

At least with this, it'll let you know if they are truly "identical".

Of course, If it is an incompatibility problem due to the sheet / workbook coming from a newer or older version than what you have installed, then you night need a re-work.
Wish I could have been more directly helpful.
Hey, what do I know?
User avatar
Lenp
Posts: 1518
Joined: Thu Oct 26, 2006 8:11 pm
Location: Maryland
Contact:

Re: Excxel Question...

Post by Lenp »

Hi CeaSar,

Thanks for the shout out! This a pretty elaborate spreadsheet that is used to track attendee's for Camp COPS, a week long day camp sponsored by the Carroll Co Sheriff's Dept, MD State Police and other local law enforcement jurisdictions.
The spreadsheet uses a barcode scanner to log the arrival and departure of the attendees, and it is supposed to post the times in a cell next to their name in the LOG tab. The last spreadsheet was in 2021 but it was not used because of the pandemic. With the 2021 version, there now are no time posts, but the 2019 version works? Several machines and Windows versions were tried and the newer one will not work, but the older one does! So to keep the Sheriff from coming after me :sad: , I used the old version and applied the fixes that were in the newest version. I really think there was some system 'downgrades' that affected something in the newer Windows versions since their copy and my archive copy both had the same bug!
I just want to compare cell formulas on one large tab, between the old and new versions, to see if I can find that elusive bug!

Thanks for your interest
Len

“To invent, you need a good imagination and a big pile of junk.” (T. Edison)
"I must be on the way to success since I already have the junk". (Me)
User avatar
CeaSaR
Posts: 1875
Joined: Sat Nov 08, 2003 1:01 am
Location: Phoenixville, PA USA
Contact:

Re: Excxel Question...

Post by CeaSaR »

Hmmm, sounds like whatever linked the scanner software to the spreadsheet wasn't actually linked or was being fed the wrong data for those cells, leaving a blank. Of course that's just an EWAG.
Hey, what do I know?
User avatar
Lenp
Posts: 1518
Joined: Thu Oct 26, 2006 8:11 pm
Location: Maryland
Contact:

Re: Excxel Question...

Post by Lenp »

The barcode scanner is just a USB input device. Instead of the scanner the barcode number can also be entered from the key board.
In fact, with a scanner you can look up a product's UPC code and find info and sellers, in Google.
I also use a scanner to scan the UPS label to send the tracking number to a customer rather than type it in'.
It's a handy, trouble free gadget that is able to read a wide variety of barcode formats, but regrettably it is not the problem here.
BTW I tried File Compare and I can't make sense of it. I kind of expected a fortune cookie response but I got a lot of gobbldy-gook!,
Way out of my pay grade!

Off tropic, I understand there's a new moderator, so here it goes...
I get emails regarding a new post to a thread I have been signed on to. I click the link and the last post there, was mine.......
Methinks the email goes out, the new, fast, moderator dumps the spam, and I get the blank page.
Absolutely no problem if it keeps the forum in check.
Congrats, and do well!!
Len

“To invent, you need a good imagination and a big pile of junk.” (T. Edison)
"I must be on the way to success since I already have the junk". (Me)
User avatar
CeaSaR
Posts: 1875
Joined: Sat Nov 08, 2003 1:01 am
Location: Phoenixville, PA USA
Contact:

Re: Excxel Question...

Post by CeaSaR »

FC just compares the bits to see if they are different. Since you saw gobbledygook, there were definitely differences. Since it's not an ASCII file, all you'll get is the code characters. At least it was confirmed.

As far as the scanner goes, it would be the link from the form to the scanner that would be faulty (imo). Doesn't matter now since the 2019 version is updated and in use.

Yeah, I hear that guy hardly ever sleeps. Apparently he sometimes even gets spammers before Dyarker can report them :shock: . Also, I hear he has to work from time to time and has to take a few hours off from scanning the spammers. :mrgreen: (BTW, if you see a suspect post but cannot see anything wrong, view the html code and look at any included links - you'll see the sneaky way they try to sneak through)

Thanks.
Hey, what do I know?
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests