PDA

View Full Version : Formula solution for repeated search in column



GTO
07-22-2012, 03:22 PM
Greetings All,

For those familiar with me, you already know that I may just well have titled the thread, "Oh dear! Mark's trying formulas!"

Now I personally detest extra 'paperwork' and especially forms that have the same data that exists in other forms, but there are occasions when it seems to make some sense. Let us say that while I find org charts to be generally "pretty", but not particularly useful - if we are adapting to a new payroll system, showing who reports to whom, may be useful.

Anyways, here are the basics and an attached small wb. Two sheets (thus far at least).

The first sheet has very basic employees' info; name, employee ID, days off and who they report to (or conversely, how many
subordinates one is responsible for).

The only formulas are some basic COUNTIF's and a search for the supervisor's name via:


=IF(ISERROR(LEFT(INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),SEARCH(",",INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2)
,1)-1 )),"",LEFT(INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),SEARCH(",",INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),1)-1
))

Now, other than (as I have done) running the entire formula and seeing if anything blows up (the ISERROR()), is there a more
efficient way?

Now, on the second sheet, named 'Formulated Chart', I am extracting/coercing the data so there are more formulas.
Again, I simply try and blow stuff up, and upon failing any KABOOM! happening, use the formula to return the supervisor's
info:


=IF(ISERROR(LEFT(INDEX(Personnel!$A$13:$B$20,MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),SEARCH(",",INDEX(Personnel!$A$13:$B$20,
MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),1)-1)),"",LEFT(INDEX(Personnel!$A$13:$B
$20,MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),SEARCH(",",INDEX(Personnel!$A$13:$B$20,
MATCH(MATCH('Formulated Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),1)-1))

In this particular formula, I have already realized that I probably should have just hard-coded in a number for the 'look
for', rather than nesting the MATCH.

Still on the second sheet, I think my biggest interest is in where I want to continue searching for matches. Taking a look
at the 'table' at AD14:AK33, and concentrating on the first column (AD), you will see that I use two formulas:


=IF(ISERROR(MATCH(Personnel!$A$13,Personnel!$G$23:$G$142,0)),"",MATCH(Personnel!$A$13,Personnel!$G$23:$G$142,0))

...in the first row, and thereafter:


=IF(ISERROR(MATCH(Personnel!$A$13,INDIRECT("Personnel!$G$"&23+SUM($AD$14:$AD14)&":$G$142"),0)),"",MATCH(Personnel!$A$13
,INDIRECT("Personnel!$G$"&23+SUM($AD$14:$AD14)&":$G$142"),0))

...which is then dragged down. You will see that the return values are then used is the various supervisors' "branches"
if-you-will. For example, in B14:D14, we enter:


=IF(AD14<>"",INDEX(Personnel!$B$23:$D$142,SUM('Formulated Chart'!$AD$14:$AD14),0),"")

...confirming via CSE.

So... back to our 'table' of helper formulas, is there a better way than INDIRECT() to keep searching? If not, that is fine
of course, I was actually quite happy to figure out how to get all the returns without example. I just wanted to ask, as if
the answer is, "Nice try Mark, but SOMEBETTERFUNCTION() would be wayyyy more efficient in continuing search.", then it is a
chance to learn :-)

Thank you so very much,

Mark

Bob Phillips
07-23-2012, 12:26 AM
What's the password?

GTO
07-23-2012, 01:20 AM
ACK!

Sorry Bob - One would think after being fairly wordy in my question, that I would have at least not forgotten that.

It is "1234"

snb
07-23-2012, 02:09 AM
Sub snb()
sn = Sheets("Personnel").Cells(23, 1).CurrentRegion.Resize(, 8)

For j = 1 To 6
c01 = ""
For jj = 1 To UBound(sn)
If sn(jj, 7) = j Then c01 = c01 & "," & jj
Next
Sheets("formulated Chart").Range(Choose(j, "B14", "H14", "N14", "T14", "B37", "H37")).Resize(UBound(Split(c01, ",")), 3) = Application.Index(sn, Application.Transpose(Split(Mid(c01, 2), ",")), Array(2, 3, 4))
Next
End Sub

PS. I cleared the content of row 22 in sheets 'Personnel' to get a proper currentregion.

If you use 1,2,3,4,5,6,7 etc. instead of I,II,III,IV you xcan use (e.g the supervisor) with this formula:


=IF(ISERROR(MATCH(C12;Personnel!$A$13:$A$20;0));"";INDEX(Personnel!$A$13:$B$20;MATCH(C12;Personnel!$A$13:$A$20;0);2))

Bob Phillips
07-23-2012, 04:45 AM
Change the values in C12, I12, etc., from roman numerals to 1, 2, etc. then use

=IF(VLOOKUP(C12,Personnel!$A$13:$D$20,2,FALSE)="","",LEFT(INDEX(Personnel!$B$13:$B$20,C12),FIND(",",INDEX(Personnel!$B$13:$B$20,C12))-1))

=IF(B13="","",INDEX(Personnel!$A$13:$D$20,C12,3))

=IF(B13="","",INDEX(Personnel!$A$13:$D$20,C12,4))

to do the lookups.

If you were prepared to show the names as per the Personnel sheet, you could simplify them even more.

Bob Phillips
07-23-2012, 04:55 AM
As for the yellow table, I hate INDIRECT, so again, change the headings to 1,2,3 , etc, and use this array formula

=IF(ISERROR(SMALL(IF(Personnel!$G$23:$G$142='Formulated Chart'!AD$13,ROW(Personnel!$G$23:$G$142)-ROW(Personnel!$G$23)+1),ROW(A1))),"",SMALL(IF(Personnel!$G$23:$G$142='Formulated Chart'!AD$13,ROW(Personnel!$G$23:$G$142)-ROW(Personnel!$G$23)+1),ROW(A1))-SUM(AD$13:AD13)+AD$13)

CodeNinja
07-23-2012, 06:08 AM
ACK!

Sorry Bob - One would think after being fairly wordy in my question, that I would have at least not forgotten that.

It is "1234"

"1234... That's the combination on my luggage..."

If anyone missed that reference, my apologies... just thought I'd get a laugh from some of you older geeks like me.

GTO
07-23-2012, 06:50 AM
...
If you use 1,2,3,4,5,6,7 etc. instead of I,II,III,IV you xcan use (e.g the supervisor) with this formula:


=IF(ISERROR(MATCH(C12;Personnel!$A$13:$A$20;0));"";INDEX(Personnel!$A$13:$B$20;MATCH(C12;Personnel!$A$13:$A$20;0);2))


Thank you snb. As to the code, I really want to try using formulas for everything except creating a sheet to email. The formula looks nifty though :-)


Change the values in C12, I12, etc., from roman numerals to 1, 2, etc. then use

=IF(VLOOKUP(C12,Personnel!$A$13:$D$20,2,FALSE)="","",LEFT(INDEX(Personnel!$B$13:$B$20,C12),FIND(",",INDEX(Personnel!$B$13:$B$20,C12))-1))

=IF(B13="","",INDEX(Personnel!$A$13:$D$20,C12,3))

=IF(B13="","",INDEX(Personnel!$A$13:$D$20,C12,4))

to do the lookups.

Thank you of course, very slick. I especially like that the second and third formulas simply check the return of the first.


If you were prepared to show the names as per the Personnel sheet, you could simplify them even more.

The real last names usually have rank in front, it gets too long (IMO at least).


As for the yellow table, I hate INDIRECT, so again, change the headings to 1,2,3 , etc, and use this array formula

=IF(ISERROR(SMALL(IF(Personnel!$G$23:$G$142='Formulated Chart'!AD$13,ROW(Personnel!$G$23:$G$142)-ROW(Personnel!$G$23)+1),ROW(A1))),"",SMALL(IF(Personnel!$G$23:$G$142='Formulated Chart'!AD$13,ROW(Personnel!$G$23:$G$142)-ROW(Personnel!$G$23)+1),ROW(A1))-SUM(AD$13:AD13)+AD$13)

I am on slow ol' laptop and it is 0630+ hours; I will have to test that bit (and a bit of studying) later, but that is "super cool!" (As mentioned before, I'm 51, so expressions like "Cool!" count!). I figured that there must be something better than INDIRECT, but was certainly at a loss as to what that "something" might be.

By the way, as the sheet (the 'chart' sheet) will be protected, it occurred to me that I could use simple custom number formats. For C12, [=1]I;General ; for I12, [=2]II;General , and so on. I think this would work for the headings of the yellow table as well :-)

@codeninja:

I do not remember who's line that was, but I seem to recall it. Thanks for the chuckle!

Mark

CodeNinja
07-23-2012, 11:14 AM
@codeninja:

I do not remember who's line that was, but I seem to recall it. Thanks for the chuckle!

Mark

http://www.youtube.com/watch?v=a6iW-8xPw3k

Bob Phillips
07-23-2012, 04:00 PM
By the way, as the sheet (the 'chart' sheet) will be protected, it occurred to me that I could use simple custom number formats. For C12, [=1]I;General ; for I12, [=2]II;General , and so on. I think this would work for the headings of the yellow table as well :-)

You could, but why are you fixated on Roman numerals? There is a good reason that Arabic numbers hold sway over Roman in the modern world.

GTO
07-23-2012, 11:44 PM
You could, but why are you fixated on Roman numerals? There is a good reason that Arabic numbers hold sway over Roman in the modern world.

Well now, I wouldn't call it fixated, you make it sound as if I may need psychotropics in the near future:drooling2 . I am sure there's more important things to worry about in MMXII. Like whether hay will be between VI and IX dollars, or will it jump. What makes you think I have a predilection for it?

By the way, you'll note that in your signatures... prolata sunt duobus latine. :goofball:

I'd better go wash and grease the chariots...

Mark