Check if one or the other cell is empty - essentially check for a mismatch 3

[MattGreer]

I'm parsing through a spreadsheet and I need to check if there's an instance when one cell has something in it but the other is blank. There are instances when both cells have something in them, and instances when both cells are blank. That's OK.

All I can come up with is something pretty ugly but it seems to work (I don't

If IsEmpty(Cell 1) And Not IsEmpty(Cell 2) OR IsEmpty(Cell 2) And Not IsEmpty(Cell 1)

What's order of operations for AND and OR statements? This works but I would have though I needed brackets around the two AND comparisons, right?

I mean, there has to be an easier or more logical way, right? This works, but is there some kind of "mismatch" function?

Thanks!!


Matt

 

[SkipVought]

Hi,

You can clarify precedents by the use of Parentheses. Stuff within Parentheses are evaluated before stuff outside Parentheses. So you'll want your two ANDs to evaluate before the OR.

BTW, I believe that your current expression would evaluate correctly, the ANDs before the OR, but I'd use Parentheses anyhow to minimize ambiguity.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Thank you for looking this over Skip. Your presence on this website is a benefit to all!

Thanks!!


Matt

 

[strongm]

As with mathematical operators, boolean operators have an order of operation. For the basic operators from highest to lowest priority is NOT, then AND, then OR

So, in your case, you get the expected result.

However, as Skip suggests, it is best to remove ambiguity with parenthesis

 

[Andrzejek]

Aside from the logic here and back to your original issue: how about filter column1 (show me blanks) and column2 (show me everything but blanks). And vice-versa.
No formula required.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Andrzejek,

I appreciate the suggestion of course. I'll give the backstory so hopefully it'll make sense.

I'm helping with a project that is taking a bunch of technical drawings, called "ISOs", that contain details of fabricated parts for a larger "module", if you will. All the fabricated parts, which consist of a list of things to be welded together, will then be bolted together to make the final machine/facility. The drawing has a 3D representation of what needs to be fabricated, dimensions, and a list of parts. I'm interested in the list of parts. Unfortunately, we don't have the source drawings, and the PDFs are inconsistent enough that if I export to any file format that Adobe knows, it's still too much of a mess for me to work with. So I'm copying the block of text that lists the parts, pasting it into a text file, running Regular Expressions on it to sort things out into a CSV file, then importing that into Excel. There are some things regex can't do, and that's what I'm doing in Excel.

For each block of parts, there's a drawing number and a module number. What this question pertains to is a bit of error checking. I have to manually type in the module number and drawing number when I'm copying out of Adobe. If I goofed and didn't type one thing or the other, there will be a blank space for one or the other. So as I run down all the rows, the code checks to see if I left something out.

Hope that helps a bit! So filtering won't work. The code is running in the background, copying drawing and module numbers down next to the corresponding parts. If I get to a mismatch, the code stops and pops up a message box to alert me to go fix the thing.

Thanks!!


Matt

 

[Andrzejek]

In my simple world I would use filters, but your world is much more complicated, so I totally understand.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

lol I guess so! I still appreciate the suggestion. Sometimes the simplest solution is the best!

Thanks!!


Matt

 

[strongm]

>There are some things regex can't do

Want a bet? _

 

[MattGreer]

> Want a bet?

Oooooh, I don't know what we could bet, but here's what I wanted to do but either didn't investigate enough or aren't smart enough to do. Here's some example text:

VP-MOD-112X
5-RW-40016-001
,,11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
,,7,"5S2CV-4""", 8,5S2CV-4"",1
,,8,"52SCV-5""", 8,52SCV-5"",1
,,16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1

Take the first and second lines and move them down to the first row that starts with two commas, between the commas. So the end result would look like this:

VP-MOD-112X,5-RW-40016-001,11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1

This sequence is repeated multiple times in each file, so regex would have to do this repeatedly. Of course, the first two lines would be different, as would the subsequent lines as far as the characters go and all.

I did manage to come up with one code to represent the 2nd number, the '5-RW' thing: ^(,,)(\d{1}-[a-zA-Z]{2}-\d{5}-\d{3})

But, actually, I don't really care about that because I can get an UltraEdit macro to take care of that. The real question I have at the moment (not a bet, I know this can be done) is this:

Look for any instance of (\sFLG) that is NOT preceded by the letters, in this specific combination and order, (\sWN). I've managed to come up with some pretty nifty things lately but for some reason this one I couldn't get working. I tried a negative lookback but, I failed.

Good luck!

Thanks!!


Matt

 

[Andrzejek]

It looks to me like a little mangled CSV file, normally with 6 or 7 elements per record, but you want to ‘shove’ record(s) with 1 element to the first ‘correct’ record with multi-elements...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

So this is a completely different topic now, but basically I have a pretty sad OCR capture of a PDF document that I have to "mangle" into a CSV file so we can re-create it in Excel, essentially. So I have a multi-step process of cleaning and wrangling the file into a format that, then, I import into Excel and finish the job. MS's implementation of RegEx is a little wonky, so I use UltraEdit to handle the task, as far as I can take it, on the text side.

Is there a RegEx forum around here? I searched but couldn't find one. BTW Andrezejek, congrats on having the most helpful posts! what an honor!

Thanks!!


Matt

 

[Andrzejek]

Thanks.
Your scenario reminds me of the process here where I work.
One department, having all electronic data, creates a report, prints it out, sends the printout to another department. The other department runs the printed report thru OCR to get the data in electronic format. A few people have jobs.

Wouldn’t be easier to just ask whoever creates the PDF to give you the same data in a format you can use? No coding required.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

That's sad and hilarious at the same time, heh. In this case, the PDFs were created by AutoCAD, but the AutoCAD source drawings aren't available.

I exported the PDFs to every format that Adobe would allow and tried to bring that into Excel. Because it was a mixture of drawing figures (these are isometric drawings with an included parts list) what ended up in Excel was a horrific mess. At the time, I didn't know enough about XML to try to work with that, but I doubt I would have achieved any more success than what I have done. Out of the 700 drawings, 500 of them had the BOM as text so I could just copy/paste into a text file. But the other 200 were scans, so that's taking a lot more time right now.



Thanks!!


Matt

 

[SkipVought]

This is a staged data cleanup task. Different tools for different situations. A cleanup on aisle 6 might require a mop and pail, a broom and scoop and a vacuum.

Here's what using IMPORT on your data would look like...
[pre]
A B C D E F G
11 5FS3S(MODIFIED) 1 5FS3S(MODIFIED) 1
7 5S2CV-4" 8 5S2CV-4"" 1
8 52SCV-5" 8 52SCV-5"" 1
16 5S2CV-3 7/8"" 6 5S2CV-3" 7/8"" 1
[/pre]
Then AutoFill the two values.
[pre]
A B C D E F G
VP-MOD-112X 5-RW-40016-001 11 5FS3S(MODIFIED) 1 5FS3S(MODIFIED) 1
VP-MOD-112X 5-RW-40016-002 7 5S2CV-4" 8 5S2CV-4"" 1
VP-MOD-112X 5-RW-40016-003 8 52SCV-5" 8 52SCV-5"" 1
VP-MOD-112X 5-RW-40016-004 16 5S2CV-3 7/8"" 6 5S2CV-3" 7/8"" 1
[/pre]

And I heartily agree with Andy, that whenever possible, find the source data to work with. Working with reports is almost always a challenge.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

the PDFs were created by AutoCAD, but the AutoCAD source drawings aren't available.

Oh trust me, I COMPLETELY agree with you Skip. If we had the source files, this would have been done two weeks ago. It's patently stupid what we're going through... But, it's given me an opportunity to learn something new so that part has been a lot of fun!

And yep, you got the setup correct there Skip. As you may have noticed there's a bit of an error on line 4. I'm sure I've fixed that somewhere, heh.

Thanks!!


Matt

 

[Andrzejek]

That new issue begs for a new thread...

Guessing here:
I have this CSV file:
[tt][blue]
VP-MOD-112X
5-RW-40016-001
,,[/blue]11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1[blue]
,,[/blue]7,"5S2CV-4""", 8,5S2CV-4"",1[blue]
,,[/blue]8,"52SCV-5""", 8,52SCV-5"",1[blue]
,,[/blue]16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1 [red]
ABCD
XYZ
,,[/red]11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1[red]
,,[/red]7,"5S2CV-4""", 8,5S2CV-4"",1[red]
,,[/red]8,"52SCV-5""", 8,52SCV-5"",1[red]
,,[/red]16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1
[/tt]
that want to make it into:
[tt]
[blue]VP-MOD-112X,5-RW-40016-001,[/blue]11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
[blue]VP-MOD-112X,5-RW-40016-001,[/blue]7,"5S2CV-4""", 8,5S2CV-4"",1
[blue]VP-MOD-112X,5-RW-40016-001,[/blue]8,"52SCV-5""", 8,52SCV-5"",1
[blue]VP-MOD-112X,5-RW-40016-001,[/blue]16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1
[red]ABCD,XYZ,[/red]11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
[red]ABCD,XYZ,[/red]7,"5S2CV-4""", 8,5S2CV-4"",1
[red]ABCD,XYZ,[/red]8,"52SCV-5""", 8,52SCV-5"",1
[red]ABCD,XYZ,[/red]16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

OK folks, I have done all that. So this isn't a request for help. It was a challenge to strongm to do in regex.

For my part, I take the CSV file that I created and I let Excel do it because I didn't know how to make regex do it. But Excel does it just fine. strongm implied there is nothing regex can't do, so I threw that to him and said "hey, can regex do this?"

So I'm good folks!! Sorry for the misunderstanding!

Thanks!!


Matt

 

[strongm]

Currently on vacation, and nowhere near a computer, let alone one with Excel and regular expressions. So won't be rising to the challenge (of my own making!) any time soon ...

 

[MattGreer]

Hehe, no worries. This thread is DONE.

Thanks!!


Matt