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

 

[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]

Row 79
[pre]
SECTOR-1 SECTOR-2 SECTOR-3 DECIMAL
orange
[/pre]

Row 152
[pre]
SECTOR-1 SECTOR-2 SECTOR-3 DECIMAL
apple CONTENT
[/pre]

This is what I see. I have no idea what you are seeing!

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

 

[SkipVought]

What is your Calculation mode?

Automatic or Manual?

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

 

[Edward_Low]

I am using manual click & drag down, from A2 and from B2.

This is in the file I just uploaded.

Results from row 78:
SECTOR-1 SECTOR-2 SECTOR-3 DECIMAL
orange
0
0
0
0
1
0
0
0
0

.. and from 151:
SECTOR-1 SECTOR-2 SECTOR-3 DECIMAL
apple CONTENT
1
1
1
1
1
1


.. with zeros and ones being under the DECIMAL header.

 

[SkipVought]

Great!

So what's the problem?

You hardly ever address the questions I ask!

What about your Calculation Mode?

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

 

[Edward_Low]

My last post shows correct results. The problem is, when I ran the formula and looked at the results, the sheet did not show these results in these columns - they showed incorrect results. This is what I saw.

... 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'.​

I clearly saw 'plum' in place of what should be 'orange', etc. I was looking at 79 - plum was the result, with no entry in C and a 1 in D.

I then cut and pasted 79 and 152 into my last reply which now I see are correct.

I can only conclude the macro is taking time to complete (it is a big file) - first giving erroneous results then completing with correct results. Scanning the page now, I do not see any errors. Before, they were everywhere.

 

[SkipVought]

It's making 18,000 calculations. It takes my Dell Inspiron about 30 seconds to finish calculating. My Excel shows a percent complete in the status along the lower margin.

Second time it took only 5 seconds.

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

 

[Edward_Low]

I know what I saw on the first run - it was about half errors. File size is only a guess as to why this occurred. Excel was crashing too. I just ran it again - looking down the rows, I see they are correct.

Thank you again for your generous help with my post! Didn't mean for it to drag out like that. I appreciate your time.

 

[SkipVought]

Make sure that either...

1) your Calculation Mode is Automatic or

2) you F9 (Calculate All) after a change in your sheet.

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

 

[Edward_Low]

Will certainly watch for that.

Thank you Skip!

Best....

 

[SkipVought]

Glad we could arrive at a satisfactory solution for you.

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

 

[MiggyD]

Glad that's resolved.

... When I ran the formula and looked at the results, the sheet did not show these results in these columns - they showed incorrect results. This is what I saw. ... which now I see are correct.

You may want to consider getting more memory or turn off any background processes while you continue using your workbook.

Good Luck
--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]

Thank you too Miggy for your help. I'm reasonably sure it was system memory causing the file to load erroneous results. I have a lot of apps open and lots of Excel files at once.

Thanks again for the help!