Fill Down Values in Column L:P

Originally Posted by bjnockle

The data in G2:G11 will be duplicated/repeated/copied down for the corresponding Sales office and Sales Person in column B and D respective. For Position -1 in column H, if all values are zeros (0), the values to be copied down would be ones (1). if the values in Position-1 are all ones (1), values to be copied down would be zeros (0). Same logic is applicable to column I. For column J, it will always have ones and the values to be copied down will be zeros.

just based on those words from you, I would assume you could make use of all of these if you looked up the concepts (which take just a second):

=> using the autofill handle
=> the IF() function
=> pasteSpecial function and paste values only by way of right clicking mouse

vba_php: looking for a formula to automate the original file. It is over 200,000 datasets. Thanks.

Originally Posted by bjnockle

1. Looking for formula to fill down/populate column L2:P46 using original data in column F:J.

2. Formula will take into account Sales Office in column B, Sales Zip in Column C and Sales Person in column D

your 2 points, 1 and 2 above make good sense, however the information you provided is very limited in terms of specifics. point #3, which I did not quote, is more detailed, however it is still a little unclear.

I have now looked at your file, but that does not help provide anything close to an answer. based on everything I've read from you, once again, I would suggest you look up the concepts I outlined in my original reply to you and attempt a solution yourself. You would benefit greatly, as everyone here would too, and it would give people here a better understanding of how to guide you in this little journey of finding a solution based on what you attempt and/or where you get stuck in the process.

vba_php:
1. I will start with Sales Zip in Column C. The Sales Zip is from C2:C46).

2a. For this sample file, you will notice that C2:C11 has Sales Zip data from 1999-2001. See below.

Please Login or Register  to view this content.

2b. The corresponding data for this Sales Zip is in F2:J11. See below.

Please Login or Register  to view this content.

2c. For column F2:F11, the formula will match each Sales Office data (B2:B11) to the corresponding value in column F2:F11. Each value will be populated for Zip Code 2002 to 2013 (C12:C46) by taking into account Sales Office column as well the Sales Person in column D (reason - there are several sales person in the original file. Only used Ben Eric in this sample file).

For example:
B2 (Sales Office - part of pull formula requirements) =10Com, D2= Ben, Eric and F2= 0.
B10=10Com, D10= Ben, Eric and F10= -

Formula: should be setup to return F2 = 0 for L10 =0. Reason being that it is the same Sales Office value. What this means is that all 10Com will be populated with zeros (0) from Zip Code 2002 to 2013 (C12:C46). Raw data is in A1:J46 and desired outcome is in L2:P46. Hope this helps.


Thanks.

vba_php:

However, some of the terms you are using to describe your data is part of the confusion here. for instance, you say zip code. however, the title of the column is sales zip! a zip code is a 5 digit number (Yes, I am aware. Remember, this is a dummy data. I made up the title. Actual title is year). to describe a locale on the USA map! LOL.

=> what business are you in? (industry)
=> what is the definition of the following column names?
Agent (=0)? - Same as Sales Agent
Non-Agent (1) - Same as Non-Sales Agent
Position-1 - Sales Agent Position
Position-2 - Sales Agent Position
Filled Position - Is Sales Agent Position Filled?

I'm a little disappointed that someone else has provided a supposed solution to this, given that I spent time with this thread. However, everyone can obviously contribute wherever they want, so I don't mind of course. Nice, Geoff. I'm not a function expert, as I've said many times, so I hope what you've got is something he can use. and since you now posted, I will stop. thanks.

GeoffW283: Outstanding approach.

Formulas in row 12 and copied down:
F12: =IFERROR(INDEX(F$2:F$11, MATCH($B12&$D12, $B$2:$B$11&$D$2:$D$11, 0)), "-") - This formula should be placed in L2 not F12 as implemented in your sample file. Desired Output is in L2:P46. A2:J46 is the raw data (formula will never be placed here).
When I adjusted the formula for Agent (=0)? Non-Agent (1), it worked. See adjusted formula below.

Please Login or Register  to view this content.

G12: =IFERROR(INDEX(G$2:G$11, MATCH($B12&$D12, $B$2:$B$11&$D$2:$D$11, 0)), "-")- This formula should be placed in M2 not G12 as implemented in your sample file. Desired Output is in L2:P46. A2:J46 is the raw data (formula will never be placed here).
When I adjusted the formula for Agent (=0)? Non-Agent (1), it worked. See adjusted formula below.

Please Login or Register  to view this content.

H12: =IF(F12="-", "-", --($H$2=0)) - NOT WORKING - SHOULD BE PLACED IN N2.
I12: =IF(G12="-", "-", --($H$2=0)) - NOT WORKING - SHOULD BE PLACED IN O2.
J12: =IF(F12="-", "-", 0) - NOT WORKING - SHOULD BE PLACED IN P2.

See attached file.


The F12 & G12 formulas are array formulas and must be committed via CTRL-SHIFT-Enter

GeoffW283: Are you able to fix:

H12: =IF(F12="-", "-", --($H$2=0)) - NOT WORKING - SHOULD BE PLACED IN N2.
I12: =IF(G12="-", "-", --($H$2=0)) - NOT WORKING - SHOULD BE PLACED IN O2.
J12: =IF(F12="-", "-", 0) - NOT WORKING - SHOULD BE PLACED IN P2.

Thanks alot for your time with this.

GeoffW283: Thanks a lot for looking at this. it is still not providing the right outcome.

For Position -1 in column H, if all values are zeros (0), the values to be copied down would be ones (1). if the values in Position-1 are all ones (1), values to be copied down would be zeros (0). Same logic is applicable to column I. For column J, it will always have ones and the values to be copied down will be zeros - see L2:P46 for desired outcome.

N2: =IF($L2="-", "-", --(H$2=0)) - H$2=0 should be L2. The formula is not providing the right outcome. Please review attached file.
O2: =IF($L2="-", "-", --(I$2=0)) - The formula is not providing the right outcome.
P2: =IF($L2="-", "-", 0) The formula is not providing the right outcome.

GeoffW283: Thanks for proposing the above formula. Still not working. I added more data and is not pulling correctly. See expected results in column R - V. See attached sample file.

Thanks

Geoffw283: All proposed formula not working correctly. Please see attached file for your review. Thanks a lot.

GeoffW283: Will i get a solution if I move it to the Commercial Services section? Thanks a lot for your effort thus far.

bebo021999: I have updated your attached file with what I think the right formula should be in column M

Please Login or Register  to view this content.

Please review and let me know what you think. Thanks.

bebo021999: Proposed formula

Please Login or Register  to view this content.

is crashing my actual file because of the datasets (over 200,000 rows). Any fix to this? Thanks