Need to populate two columns based on content of adjacent two columns. 2

[Edward_Low]

Hi Forum,

I hope this isn't too much to ask. I am trying to find a formula or VBA for the following:

There are four columns 1000s+ deep - A, B, C and D.

C may or may not contain a value. D contains 0 or 1. The formula needs to enter data into A and B, depending on what is in C and D.

There are four possible scenarios and their responses are:


1: Nothing is entered in C and there is a '1' found in column D. - RESPONSE: Enter 'orange' in column A.
2: Nothing is entered in C and there are only zeros in column D. - RESPONSE: Enter 'plum' in column A.
3: Something is entered in C and there is a '1' found in column D. - RESPONSE: Enter 'apple' in column A.
4: Something is entered in C and there are only zeros in column D. - RESPONSE: Enter 'raisin' in column A, and 'cherry' in column B.


There are no blank rows between any section. Any one of the four possible scenarios may exist in any section.

Thanks in advance for any help.

http://files.engineering.com/getfil...d108f4&file=MACRO_TO_MATCH_COLUMNS_101116.xls

http://files.engineering.com/getfil...a0&file=MACRO_TO_MATCH_COLUMNS_101116.xls.jpg

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

.. so on a sheet, I have 10,000+ rows, columns A, B, C and D, arranged in sections with the 'SECTOR' and 'DECIMAL' headers. A and B have only these header labels - no other content. Column C may or may not have a value in the cells immediately below the SECTOR-3 header. Column D has either only zeros in the cells below the DECIMAL header, or, contain at least one '1'.

I have the file you uploaded (thank you for that.) I see the formulas you entered in A and B, in the cells immediately below the SECTOR-1 and SECTOR-2 header labels.

What do I do next to apply the formulas to my sheet?

 

[SkipVought]

1) copy the EdLow function to a Module in your workbook, if it's not already there.

2) this formula, [tt]=EdLow($C2,$D2,B$1)[/tt], should be pasted into cell B2. Them copy B2 and paste everywhere you need the results

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

I click Alt/11. The VB window opens. In it is the EdLow function.

Back to my worksheet, I enter =EdLow($C2,$D2,B$1) into a blank cell and drag it down. It creates a column of 'cherry' with spaces, at various intervals, regardless of what is in B or C.

 

[SkipVought]

I click Alt/11. The VB window opens. In it is the EdLow function.

There are several different objects in the VB Window, (ctrl+R): Microsoft Excel Objects, including Worksheet Objects and the ThisWorkbook Object.
There may also be a Modules Object, in which will be Modules Object(s). In one of these Modules is where your EdLow function needs to be stored (pasted). If it is anywhere else, CUT the code and PASTE into a Module.

Back to my worksheet, [highlight #FCE94F]I enter =EdLow($C2,$D2,B$1) into a blank cell[/highlight] and drag it down. It creates a column of 'cherry' with spaces, at various intervals, [highlight #FCAF3E]regardless of what is in B or C.[/highlight]

[highlight #FCE94F]This[/highlight] is incorrect! The ONLY cell that this formula can be pasted in in B2 (the references tell you where it needs to be: row 2, column B.)
[highlight #FCAF3E]Don't you mean C or D.[/highlight]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Sorry- yes, I meant to say C or D above.

Alt/11 opens the editor. The pane is Modules with the module inside it so this appears correct.

OK - I enter the code =EdLow($C2,$D2,B$1) into B2.

What then? How is this then applied to the sheet?

 

[SkipVought]

Copy B2 and paste where ever you need.

From then on copy formula from any cell, paste or drag at will.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

I entered the formula into B2 as specified. I then copied B2 and pasted it into J2 and dragged it down 2000 rows.

Result: #NAME? appears in B2, B3, B4, B5, B6, B7, B8, B9 - and, B18, B26 - and B37, B38, B39, B40, B41, B42, B43 and B44.

I do not see how this will apply down to 10000+ rows.

 

[SkipVought]

Woah!

YOUR specifications! With certain data in columns C & D, return values to columns A & B!

What's with column J???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

You said "From then on copy formula from any cell, paste or drag at will." So I used J. What column should I have used?

 

[SkipVought]

That deserves no reply!

Here's a modified EdLow function in this upload.

Put the formula in row 2: A & B. Then copy all the way down through all your 1000 rows of data.

Copy anywhere else if you like.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Thank you for your patience in helping with my post. It is appreciated. I thought for a moment that the macro worked. It returned results all the way to 9000+ rows. Then I noticed, the results were nearly all wrong - a mix of correct and incorrect returns. Sections with the same combination of B and C content would give different results. The macro is also crashing Excel (2003). Not sure why but if I open one or two more files after running the macro, the whole program comes to a screeching halt.

 

[MiggyD]

AHH! Therein lies the problem. You are not communicating clearly. Even I have a hard time understanding.

I am guessing you mean the content combination of C and D; not ...

... combination of B and C content would ...

If you are using your original workbook. You really shouldn't until you understand what is happening, why it works one way and not the other, and how to modify it to your needs.

1: Nothing is entered in C and there is a '1' found in column D. - RESPONSE: Enter 'orange' in column A.
2: Nothing is entered in C and there are only zeros in column D. - RESPONSE: Enter 'plum' in column A.
3: Something is entered in C and there is a '1' found in column D. - RESPONSE: Enter 'apple' in column A.
4: Something is entered in C and there are only zeros in column D. - RESPONSE: Enter 'raisin' in column A, and 'cherry' in column B.

The condition you originally posted uses calculations [highlight #EDD400]almost similar[/highlight] to an XOR function. Which I haven't use since QuickBASIC 4.6! and even then I avoided it as much as possible.

Essentially its like this **Note: I'm using Skip's cleaned-up version of your workbook from here on out.

Plum   = C + D (where: C = "" and D = 0)
Orange = C + D (where: C = "" and D = 1)
Apple  = C + D (where: C = "word" and D = 1)
Raisin = C + D (where: C = "word" and D = 0)
Cherry = Raisin (TRUE)

If this is correct then you want something similar to the following logic:

if (C = Empty) + (D = 0) then A = Plum & B = ""
if (C = Empty) + (D = 1) then A = Orange & B = ""
if (C <> Empty) + (D = 1) then A = Apple & B = ""
if (C <> Empty) + (D = 0) then A = Raisin & B = Cherry

Is This logic correct? Is this similar as to what you would like your code to do?
Are we on the same page now? Or am I off the mark?

LMK

--MiggyD


"If a tree falls in the forest and no one is around, does it make a sound?" I finally got the REAL answer!

 

[Edward_Low]

Hi Miggy,
Thank you, and yes, I think you are on mark. I thought I was careful to avoid any misunderstanding with my post but I know how things can be misinterpreted.

Plum = C + D (where: C = "" and D = 0)
Orange = C + D (where: C = "" and D = 1)
Apple = C + D (where: C = "word" and D = 1)
Raisin = C + D (where: C = "word" and D = 0)
Cherry = Raisin (TRUE)

Just to reiterate it in my words. My way of putting it is:

(Nothing and a '1' = orange.)
(Nothing and only zeros = plum.)
(Something and a '1' = apple.)
(Something and only zeros = raisin + cherry.)

By '1', I mean any number of 1s in any position. It could be just one 1, or all 1s in the section.


Another example: nothing is entered under SECTOR-3 (in a section), AND only zeros are in column D - then, 'plum' must be entered once, in the cell immediately under SECTOR-1 in column A.

Another example: Something is entered in column C under SECTOR-3, AND, column D contains a numeral '1' somewhere. Then, 'apple' must be entered in the cell immediately under SECTOR-1, column A.

Likewise for the other two possibilities.

Apologies if I was not clear on this at any point.

 

[SkipVought]

Please upload your workbook with 9000 rows not working.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Uploaded....

 

[Edward_Low]

... 79, for example, has 'plum' (has blank in C79 and a '1' in the section, so would be 'orange').

152 has 'raisin' 'cherry', but has something in C152 and 1s in D, so would be 'apple'.

 

[SkipVought]

Please tell me a specific instance (row) of an incorrect result: What you get and what you expect.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Yes, as above - 79 and 152.

 

[SkipVought]

79 and 152 WHAT?

What to you get?

What do you expect?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]