Revision history for ResearchSpreadsheetFunctions


Revision [15160]

Last edited on 2015-04-28 16:27:04 by MichaelM
Additions:
Index Match - also see MatchIDs
Deletions:
Index Match

Revision [14672]

Edited on 2015-04-03 10:56:35 by MichaelM [formatting and style edits]
Additions:
Index Match
Deletions:






""<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/wICRSkL3ph-VgEzQH9J1DhRgsopbd17E158OGXfLTps?feat=embedwebsite"><img src="https://lh6.googleusercontent.com/-jrW3IRysyDk/UoJounpRFVI/AAAAAAAAAGs/TAiwA7gcmqc/s800/Screenshot-RID6952_Texas_Eagle_Forum.xls%2520-%2520OpenOffice.org%2520Calc.png" height="455" width="800" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/103924738085683949323/November122013?authuser=0&feat=embedwebsite">November 12, 2013</a></td></tr></table>""



""<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/AVRyOGlySWWHRJfTXgL9BgGN2fc_X3gkuA9Xerukkdg?feat=embedwebsite"><img src="https://lh5.googleusercontent.com/-vmiK7AIbTwU/Un1Y0rKpxpI/AAAAAAAAAGM/jiqrZ10CMbc/s800/state_id-function.png" height="423" width="800" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/103924738085683949323/November82013?authuser=0&feat=embedwebsite">November 8, 2013</a></td></tr></table>""

...or brute force copy pasta.
%%AL
AL
AL
AL
AL
AL
AL
AL
AL
AK
AK
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AR
AR
AR
AR
AR
AR
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CO
CO
CO
CO
CO
CO
CO
CO
CO
CT
CT
CT
CT
CT
CT
CT
DE
DE
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
HI
HI
HI
HI
ID
ID
ID
ID
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IN
IN
IN
IN
IN
IN
IN
IN
IN
IN
IN
IA
IA
IA
IA
IA
IA
IA
KS
KS
KS
KS
KS
KS
KY
KY
KY
KY
KY
KY
KY
KY
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
MD
MD
MD
MD
MD
MD
MD
MD
MD
MD
MA
MA
MA
MA
MA
MA
MA
MA
MA
MA
MA
MA
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MN
MN
MN
MN
MN
MN
MN
MN
MN
MN
MS
MS
MS
MS
MS
MS
MO
MO
MO
MO
MO
MO
MO
MO
MO
MO
MO
MT
MT
NE
NE
NE
NE
NE
NV
NV
NV
NV
NV
NH
NH
NH
NH
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NM
NM
NM
NM
NM
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
ND
ND
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OK
OK
OK
OK
OK
OK
OK
OR
OR
OR
OR
OR
OR
OR
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
RI
RI
RI
RI
SC
SC
SC
SC
SC
SC
SC
SC
SD
SD
TN
TN
TN
TN
TN
TN
TN
TN
TN
TN
TN
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
UT
UT
UT
UT
UT
VT
VT
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
WA
WA
WA
WA
WA
WA
WA
WA
WA
WA
WA
WV
WV
WV
WV
WV
WI
WI
WI
WI
WI
WI
WI
WI
WI
WI
WY
WY
%%



Index Match beeotchez

Revision [10807]

Edited on 2014-05-01 20:40:11 by SarkP [formatting and style edits]
Additions:
Index Match beeotchez
%%=INDEX(index_match.A$2:A$533,MATCH(B2,index_match.B$2:B$533,0))%%

Revision [10379]

Edited on 2013-11-13 14:41:31 by SarkP [formatting and style edits]
Additions:
...or brute force copy pasta.
%%AL
AL
AL
AL
AL
AL
AL
AL
AL
AK
AK
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AZ
AR
AR
AR
AR
AR
AR
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CA
CO
CO
CO
CO
CO
CO
CO
CO
CO
CT
CT
CT
CT
CT
CT
CT
DE
DE
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
FL
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
GA
HI
HI
HI
HI
ID
ID
ID
ID
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IL
IN
IN
IN
IN
IN
IN
IN
IN
IN
IN
IN
IA
IA
IA
IA
IA
IA
IA
KS
KS
KS
KS
KS
KS
KY
KY
KY
KY
KY
KY
KY
KY
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
LA
MD
MD
MD
MD
MD
MD
MD
MD
MD
MD
MA
MA
MA
MA
MA
MA
MA
MA
MA
MA
MA
MA
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MI
MN
MN
MN
MN
MN
MN
MN
MN
MN
MN
MS
MS
MS
MS
MS
MS
MO
MO
MO
MO
MO
MO
MO
MO
MO
MO
MO
MT
MT
NE
NE
NE
NE
NE
NV
NV
NV
NV
NV
NH
NH
NH
NH
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NJ
NM
NM
NM
NM
NM
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NY
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
NC
ND
ND
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OH
OK
OK
OK
OK
OK
OK
OK
OR
OR
OR
OR
OR
OR
OR
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
PA
RI
RI
RI
RI
SC
SC
SC
SC
SC
SC
SC
SC
SD
SD
TN
TN
TN
TN
TN
TN
TN
TN
TN
TN
TN
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
TX
UT
UT
UT
UT
UT
VT
VT
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
VA
WA
WA
WA
WA
WA
WA
WA
WA
WA
WA
WA
WV
WV
WV
WV
WV
WI
WI
WI
WI
WI
WI
WI
WI
WI
WI
WY
WY
%%

Revision [10378]

Edited on 2013-11-12 10:33:18 by SarkP [formatting and style edits]
Additions:
%%=MID(B2,FIND(" ",B2)+1,LEN(B2)-FIND(" ",B2))%%

Revision [10377]

Edited on 2013-11-12 10:32:07 by SarkP [formatting and style edits]
Additions:
Using FIND to separate firstname.
""<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/wICRSkL3ph-VgEzQH9J1DhRgsopbd17E158OGXfLTps?feat=embedwebsite"><img src="https://lh6.googleusercontent.com/-jrW3IRysyDk/UoJounpRFVI/AAAAAAAAAGs/TAiwA7gcmqc/s800/Screenshot-RID6952_Texas_Eagle_Forum.xls%2520-%2520OpenOffice.org%2520Calc.png" height="455" width="800" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/103924738085683949323/November122013?authuser=0&feat=embedwebsite">November 12, 2013</a></td></tr></table>""

Revision [10375]

Edited on 2013-11-08 14:28:06 by SarkP [formatting and style edits]
Additions:
%%=IF(LEN(B12)>2,INDEX("""state_abbrev chart""",MATCH("""selected state""",0)),A11)%%
Deletions:
%%=IF(LEN(B12)>2,INDEX("""state_abbrev chart""",MATCH("""selected state""",0))A11)%%

Revision [10374]

Edited on 2013-11-08 14:21:39 by SarkP [formatting and style edits]
Additions:
""<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/AVRyOGlySWWHRJfTXgL9BgGN2fc_X3gkuA9Xerukkdg?feat=embedwebsite"><img src="https://lh5.googleusercontent.com/-vmiK7AIbTwU/Un1Y0rKpxpI/AAAAAAAAAGM/jiqrZ10CMbc/s800/state_id-function.png" height="423" width="800" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/103924738085683949323/November82013?authuser=0&feat=embedwebsite">November 8, 2013</a></td></tr></table>""
Deletions:
""<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/AVRyOGlySWWHRJfTXgL9BgGN2fc_X3gkuA9Xerukkdg?feat=embedwebsite"><img src="https://lh5.googleusercontent.com/-vmiK7AIbTwU/Un1Y0rKpxpI/AAAAAAAAAGM/jiqrZ10CMbc/s640/state_id-function.png" height="339" width="640" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/103924738085683949323/November82013?authuser=0&feat=embedwebsite">November 8, 2013</a></td></tr></table>""

Revision [10373]

Edited on 2013-11-08 14:21:06 by SarkP [formatting and style edits]
Additions:
""<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/AVRyOGlySWWHRJfTXgL9BgGN2fc_X3gkuA9Xerukkdg?feat=embedwebsite"><img src="https://lh5.googleusercontent.com/-vmiK7AIbTwU/Un1Y0rKpxpI/AAAAAAAAAGM/jiqrZ10CMbc/s640/state_id-function.png" height="339" width="640" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/103924738085683949323/November82013?authuser=0&feat=embedwebsite">November 8, 2013</a></td></tr></table>""

Revision [10372]

Edited on 2013-11-08 14:13:12 by SarkP [formatting and style edits]
Additions:
To carry over state abbreviations.
%%=IF(LEN(B12)>2,INDEX("""state_abbrev chart""",MATCH("""selected state""",0))A11)%%

Revision [9950]

Edited on 2013-10-04 15:49:28 by SarkP [formatting and style edits]
Additions:
%%=IF(AND(A2="U.S. House", OR(B2="AK",B2="MT",B2="ND",B2="SD",B2="ID",B2="WY",B2="DE",B2="VT"), VALUE(C2)=1),"At-Large",C2)%%
Deletions:
%%=IF(AND(D102="U.S. House", OR(E102="AK",E102="MT",E102="ND",E102="SD",E102="ID",E102="WY",E102="DE",E102="VT"), VALUE(F102)=1),"At-Large",F102)%%

Revision [9949]

Edited on 2013-10-04 15:47:15 by SarkP [formatting and style edits]
Additions:
To find the value of the 2nd space in a cell.
%%=FIND(" ",A2,FIND(" ",A2)+1)%%
To find the value of the 3rd space in a cell.
%%=FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1)%%

Revision [9254]

Edited on 2013-05-30 08:20:06 by NicholasGann [formatting and style edits]
Additions:
%%=IF(AND(D102="U.S. House", OR(E102="AK",E102="MT",E102="ND",E102="SD",E102="ID",E102="WY",E102="DE",E102="VT"), VALUE(F102)=1),"At-Large",F102)%%
Deletions:
%%=IF(AND(D102="U.S. House", OR(E102="AK",E102="MT",E102="ND",E102="SD",E102="ID",E102="WY",E102="DE",E102="VT"), F102=1),"At-Large",F102)%%

Revision [9244]

The oldest known version of this page was created on 2013-05-29 10:44:55 by NicholasGann [formatting and style edits]
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki