• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Main table filling after scanning info in 2 supporting tables

Krish1958

New Member
Hello Everyone,

Krish here. I am not fully conversant in the multitudes of Excel formulas and their combinations. I have managed to pick up usage of some formulas e.g. "IF", "Sumifs", basic VLookup and other basic stuff.

I have a table part of which I have attached. It is a table where farm fertilizer rates have to filled in the main table (Table 1) after scanning data from Table 2 (leaf analytical data) and from Table 3 that specifies the rates of fertilizer (TSP, HiK+, SOA, MOP, BOR, KIES, HiK++) based on the leaf nutrient values in Table 2.

The farm is a large farm divided into blocks (BLK). The blocks have been grouped (colour coded in Table 1) and from each group, 1 block has been selected for leaf sampling and analysis of nutrient content (N,P,K, Mg, B). The leaf analytical information from this block is assumed to represent the other blocks in the group.

What I would like to achieve is to quickly fill in the fertilizer rates in Table 1 without having to do it manually. So the formula will scan the BLK in Table 1, maybe match with BLK in Table 2, match nutrient in Table 2 with Table nutrient range, then pick out the fertilizer from the top of Table 1 matching with the fertiliser in Table 3 and return the corresponding fertilizer value shown for the nutrient ranges.

Once the fertilizer inputs for the leaf sampled block are inputted into the corresponding row in Table 1, then I can copy it for the other blocks in the group. Or there might be a formula to replicate it based on the color of the cells (maybe?).

I have tried Vlookup combined with Match and Index combined with Match but to no avail. Most times I get a "Value!" or "Ref!" return. Also, I gotten the "Too many arguments" scolding from Excel!

If anyone can guide me, I would be grateful. There is a total of about 350 blocks in leaves from the plants were analysed. Manually inputting fertilizer requirements will be tedious, as I have done in the past!!

Thank you and please keep safe.

Regards,
Krish
 

Attachments

p45cal

Well-Known Member
This is quite complicated; you have to look at Table 1, see what fertiliser is involved (TSP, SOA etc.), find out what nutrient that's involved with from Table 2, then use the BLK number and the nutrient to see what the analysis value was, then use that value in Table 3 to lookup what rate to apply.
It's made more complex by BOR having 2 nutrients (B and Mg).

In the attached, on sheet Sheet1 (2), what I've done there is to separate BOR into BORb (or B) and BORm (for Mg) and added a column in Table 1. As it happens, all the analyses for B in all the BLKs were all >=15 so the values for BORb are all 0. I have no data for KIES.

I've added columns A & S because it's impossible to work with coloured cells in formulae. I've added column AC which is a translation to pure numbers of the text in column AB, as well as rearranging Table 3 (which is missing data for KIES).

I've added a formula to cell E6, and copied this down to E25, then copied across to columns G, I, J, K, L & N. You need to check the values there.

This might be easier for the user by designing a user-defined function in VBA. Power Query might also be a solution but I haven't explored that yet.
 

Attachments

Krish1958

New Member
Hi p45cal,

Thank you very much for your assistance. Wow, a complicated formula indeed which I am going to spend a while to understand. I would never have figured it out

My bad on the KIES data in Table 3. In a hurry, I accidentally placed the 2 KIES values (1.00 & 0.50) under the BOR column instead of the KIES column. My absolute and sincerest apologies for any and all confusion/ irritation that may have caused!

As for VBA, I must confess it will be very slow going for me. I have not attempted Power Query. That will also be a bit of a journey for me. At 62yo, I an old fart to figure out complicated formulae but I will certainly give it a go.

Again, thank you very, very much.
 

p45cal

Well-Known Member
the 2 KIES values (1.00 & 0.50) under the BOR column instead of the KIES column
It didn't cross my mind, but I should've guessed!

Have we got the full range of nutrients and fertilisers?; While each nutrient can be associated with multiple fertilisers (K with HIK+, HIK++ & MOP), at the moment each fertiliser is associated with only one nutriet - is that always the case? If so there'll be ways of simplifying the formula and perhaps no need to rearrange Table 3.
 

Peter Bartholomew

Well-Known Member
I seem to have got it going but I used LET to organise the calculation :(
Code:
= LET(
  sample,INDEX(AnalysisData, SampleNumber, 0),
  requiredLevel, COUNTIFS(SampledNutrient,Nutrient,sample,">="&LBound,sample,"<"&UBound),
  fertilizerRate, MMULT(TRANSPOSE(requiredLevel), --Fertilizer),
  IFERROR( INDEX(fertilizerRate, MATCH(Application, FertilizerName, 0)), "") )
Note: In the attached file I have unloaded the local names into named formulas so the sheet may work with an array formula on each row.
 

Attachments

bosco_yip

Excel Ninja
.............My bad on the KIES data in Table 3. In a hurry, I accidentally placed the 2 KIES values (1.00 & 0.50) under the BOR column instead of the KIES column. My absolute and sincerest apologies for any and all confusion/ irritation that may have caused!
...........
Hi,

'Here's another formula solution but without changing your 3 original tables layout and without using helper columns.

Some modification :

1) Shift "Table 3" data from AF18:AF19 to AG18:AG19 as per Post #.3 mentioned.

2) Convert range in "BLK" B6:B25 and "Blok" S6:S11 from "Text value" into "Numeric value", by using "Text to Columns" Excel built-in function

Then,

In E6, formula copied across right and down :

=IF(E$5="","",INDEX($T$6:$X$11,MATCH(LOOKUP($B6,{0;4;8;12;15;19},$S$6:$S$11),$S$6:$S$11,0),MATCH(INDEX($Z$6:$Z$20,MATCH(1,INDEX(0+(INDEX($AB$6:$AH$20,0,MATCH(E$5,$AB$5:$AH$5,0))<>""),0),0)),$T$5:$X$5,0)))

Remark :

the above formula using a hardcode array {0;4;8;12;15;19} within the Lookup function, In order to distinguish the Blocks color group.

72030
 

Attachments

Krish1958

New Member
It didn't cross my mind, but I should've guessed!

Have we got the full range of nutrients and fertilisers?; While each nutrient can be associated with multiple fertilisers (K with HIK+, HIK++ & MOP), at the moment each fertiliser is associated with only one nutriet - is that always the case? If so there'll be ways of simplifying the formula and perhaps no need to rearrange Table 3.
HIK+ is a compound fertilizer containing N, P, K and Mg as the main nutrients plus a bit of B. However, as the leaf analysis indicates that the nutrient levels are not quite in the acceptable ranges and thus there is a need for TSP (to supply extra P), SOA (extra N) and KIES (extra Mg). The fertilizer program also needs to take into account overall cost. So, at the moment, a combination of fertilizers are needed and the best option. When the nutrient levels have improved, then it might be possible to use just HIK+ but I think it will not be possible due to the nature of the trees which stand for about 20 years (economic lifespan).
 

Krish1958

New Member
Hi,

'Here's another formula solution but without changing your 3 original tables layout and without using helper columns.

Some modification :

1) Shift "Table 3" data from AF18:AF19 to AG18:AG19 as per Post #.3 mentioned.

2) Convert range in "BLK" B6:B25 and "Blok" S6:S11 from "Text value" into "Numeric value", by using "Text to Columns" Excel built-in function

Then,

In E6, formula copied across right and down :

=IF(E$5="","",INDEX($T$6:$X$11,MATCH(LOOKUP($B6,{0;4;8;12;15;19},$S$6:$S$11),$S$6:$S$11,0),MATCH(INDEX($Z$6:$Z$20,MATCH(1,INDEX(0+(INDEX($AB$6:$AH$20,0,MATCH(E$5,$AB$5:$AH$5,0))<>""),0),0)),$T$5:$X$5,0)))

Remark :

the above formula using a hardcode array {0;4;8;12;15;19} within the Lookup function, In order to distinguish the Blocks color group.

View attachment 72030
Hi Bosco,
Thank you very much. I like your idea of distinguishing the Blocks color grouping without helper columns.

I am not sure why but in your working, the values that need to appear in Table 1 are not correct. The values that should appear are shown in Table 3 based on what the nutrient levels for the block as shown in Table 2.
 
Last edited:

Krish1958

New Member
I seem to have got it going but I used LET to organise the calculation :(
Code:
= LET(
  sample,INDEX(AnalysisData, SampleNumber, 0),
  requiredLevel, COUNTIFS(SampledNutrient,Nutrient,sample,">="&LBound,sample,"<"&UBound),
  fertilizerRate, MMULT(TRANSPOSE(requiredLevel), --Fertilizer),
  IFERROR( INDEX(fertilizerRate, MATCH(Application, FertilizerName, 0)), "") )
Note: In the attached file I have unloaded the local names into named formulas so the sheet may work with an array formula on each row.
Dear Peter,

Thank you very much for your input. It looks simple once the coding has been done (as also suggested by p45cal in Post #2).
 

bosco_yip

Excel Ninja
Hi Bosco,
Thank you very much. I like your idea of distinguishing the Blocks color grouping without helper columns.

I am not sure why but in your working, the values that need to appear in Table 1 are not correct. The values that should appear are shown in Table 3 based on what the nutrient levels for the block as shown in Table 2.
Hi,

Attached a revised file for the result value being as shown in "Table 3"

Regards
 

Attachments

Krish1958

New Member
Hi,

Attached a revised file for the result value being as shown in "Table 3"

Regards
Bosco,
Thank you very much. I like the fact that helper columns are not really required in your method seeing that the actual file is huge and divided into 3 farms and further into sub-divisions within each farm. It takes a while to ensure that the helper columns and references are correct .

Your revised table is picking up the fertilizer values as needed.

Thank you very much
 

Peter Bartholomew

Well-Known Member
I probably should have 'confessed'. Although the 'Nutrient Level' column looks similar to the original, it is actually two columns with the lower and upper bounds separate. The appearance is all 'smoke and mirrors, achieved with number formatting. I have used an '∞' symbol because it is the appropriate mathematical notation but, in reality, any text field or a large number could be used.

72036
 

bosco_yip

Excel Ninja
Bosco,
Thank you very much. I like the fact that helper columns are not really required in your method seeing that the actual file is huge and divided into 3 farms and further into sub-divisions within each farm. It takes a while to ensure that the helper columns and references are correct .

Your revised table is picking up the fertilizer values as needed.

Thank you very much
'In Order to shorten the formula,

Try to use Define Name range by :

Select E6 >> Define Name >>

Name : Refers to

NutrientContent : =INDEX($Z$6:$Z$20,MATCH(1,INDEX(0+(INDEX($AB$6:$AH$20,0,MATCH(E$5,$AB$5:$AH$5,0))<>""),0),0))

NutrientData : =INDEX($T$6:$X$11,MATCH(IF($B6=14,20,LOOKUP($B6,{0;4;8;12;15;19},$S$6:$S$11)),$S$6:$S$11,0),MATCH(NutrientContent,$T$5:$X$5,0))

NutrientRange : =0+MID(OFFSET($AA$5,MATCH(NutrientContent,$Z$6:$Z$20,0),,COUNTIF($Z$6:$Z$20,NutrientContent)),3,4)

FertiliserRate : =OFFSET($AA$5,MATCH(NutrientContent,$Z$6:$Z$20,0),MATCH(E$5,$AB$5:$AH$5,0),COUNTIF($Z$6:$Z$20,NutrientContent))

Then,

In E6, formula copied across and down :

=IF(E$5="","",LOOKUP(NutrientData,NutrientRange,FertiliserRate))

Edit : Please ignore my file in post #10 and using this new and revised file.

Regards
 

Attachments

Last edited:

p45cal

Well-Known Member
@bosco_yip , I suspect there may be a problem with row 19 of your solution, which is showing up as a possible errant result in cell M19 which I think should be 0. When I select that cell and press F5 on the keyboard (Go to) and enter 'NutrientData' it sends me to cell W9 and I think it should send me to W11?

edit post posting: scratch all that, I was looking at the wrong file - sorry.

second edit post posting: your definition of the Name NutrientData and how you've coped with the exception BLK14 raises an eyebrow just a little! ;)
 
Last edited:

bosco_yip

Excel Ninja
@bosco_yip , I suspect there may be a problem with row 19 of your solution, which is showing up as a possible errant result in cell M19 which I think should be 0. When I select that cell and press F5 on the keyboard (Go to) and enter 'NutrientData' it sends me to cell W9 and I think it should send me to W11?
@p45cal , Thank you for your advice and checking.

I have make adjusting to the Lookup hardcode array in the Define Name of "NutrientData" to the above post #13 and the new file with revised formula.

Edit : the error is due to OP put in 2 separated range B19, and B24:B25 in the same brown color.

Regards
Bosco
 

p45cal

Well-Known Member
Aaaagh, now I see what's been going on, we've both been editing our previous messages
My concern would be that given the OP has said that there are a lot more blocks that there could be some very careful adjustment of the NutrientData name required in the future. My feeling is, depending on the OP's numbering system for blocks, that it might be easier (and more transparent) to adjust helper columns, despite the OP's reservations about the time required to do so in msg#11.
 
Last edited:

p45cal

Well-Known Member
Despite my having received a comprehensive answer from Krish to my question in msg#4
at the moment each fertiliser is associated with only one nutrient - is that always the case?
I confess to not being sure of the answer!

Regardless, in an effort to shorten the formula a little (sure I could shorten more with defined names, I know) the attached has a couple more offerings where I've added a row to Table 1 indicating the nutrient associated with each fertiliser (row 6) which will save a lookup.
The only difference between the 2 sheets in the attached is that Table 3 in the sheet Solution3 is much the same as the original Table 3
 

Attachments

p45cal

Well-Known Member
Another offering, requiring no changes at all to the original tables. This one looks at the colours in the cells, so make sure they're exactly the same in both tables where they're supposed to correspond.
I'm not going to bother with a Power Query solution.
 

Attachments

Krish1958

New Member
Thank you p45cal and Bosco for the wonderful discussions and efforts to simplify the formula. I need to study them slowly to understand.

@Bosco: in your formula (post 13), there is a Lookup portion with numbers 0;4;8;12;15;19. What do they refer to, pls? Also, in post 15, you mentioned OP. What is this?

Thank you again
 

Krish1958

New Member
Another offering, requiring no changes at all to the original tables. This one looks at the colours in the cells, so make sure they're exactly the same in both tables where they're supposed to correspond.
I'm not going to bother with a Power Query solution.
Your formula:
=IFERROR(myrate($B6,E$5,$S$5:$X$11,$Z$5:$AH$20),"")

Where did you define "myrate"? I am unable to locate it. Curiosity on my part and path to understanding
 

Krish1958

New Member
@p45cal

I was messing about with the BLK values in Table 1 in trying to understand the workings and this happened i.e. even when there was no common factor between Table 1 and Table 2, the formula for some reason did not return an error message i.e. it still maintained the fertilizer rates in Table 1. I could not figure out why. Apologies for keeping you on this thread far longer than needed!
 

Attachments

Last edited:

Krish1958

New Member
@ bosco-yip

Similar to my post #21 to p45cal, I did the same to your latest workings in Post #13.

Question 1: I deleted the BLK vales in Table 1 but not in Table 2. The formulas picked and returned fertilizer rates.

Question 2: If I replace some of the BLK values in Table 1 and match them in Table 2, "#N/A" is returned in the blocks which were changed in Table 1 and Table 2. I am thoroughly confused!! Sorry to trouble you.
 

Attachments

p45cal

Well-Known Member
Table 1 and Table 2, the formula for some reason did not return an error message i.e. it still maintained the fertilizer rates in Table 1. I could not figure out why.
This is because it ONLY looks at the colours in the cells and ignores numbers completely:
This one looks at the colours in the cells, so make sure they're exactly the same in both tables where they're supposed to correspond.
Where did you define "myrate"? I am unable to locate it.
It's in Module1 as a user-defined function (press Alt+F11 to open the vb editor).
 

p45cal

Well-Known Member
While testing my response to your last question I noticed that once a colour has been changed it is difficult to get the sheet to re-calculate, so I've made a very small tweak to the user-defined function. Now, once you've changed a colour it should update automatically, but if it doesn't you can make it recalculate by pressing F9 on the key board.
Alteration in attached.
 

Attachments

Krish1958

New Member
This is because it ONLY looks at the colours in the cells and ignores numbers completely:

It's in Module1 as a user-defined function (press Alt+F11 to open the vb editor).
I should have read your explanation again after messing about. Now that you pointed my nose to the color aspect, it makes sense. Thank you.
 
Top