Hello friends,
I'm fairly new to this logic stuff.
I have a list of part #s that i need to group together and sort by their corresponding dates. The formula I've written does what I'd like it to do (assign a value to part groups that i can sort by first before sorting by the date) , but is too large/has too many nested functions to be applied.
This formula is going to be used with multiple queries from a database that updates regularly.
EXAMPLE OF DATA(6000+ entries)
\1
This is the breakdown of my formula, I've bolded the parts that aren't constant
=IF(AND(LEFT(D2,5)>="S2000"LEFT(D2,5)<"S2005"),1,IF(continues)
The "S" range isnt always +5.
=IF(AND(LEFT(D2,5)>="S2000",LEFT(D2,5)<"S2005"),1,IF(AND(LEFT(D2,5)>="S2005",LEFT(D2,5)<"S2010"),2,IF(AND(LEFT(D2,5)>="S2010",LEFT(D2,5)<"S2015"),3,IF(AND(LEFT(D2,5)>="S2015",LEFT(D2,5)<"S2020"),4,IF(AND(LEFT(D2,5)>="S2020",LEFT(D2,5)<"S2025"),5,IF(AND(LEFT(D2,5)>="S2025",LEFT(D2,5)<"S2030"),6,IF(AND(LEFT(D2,5)>="S2030",LEFT(D2,5)<"S2045"),7,IF(AND(LEFT(D2,5)>="S2045",LEFT(D2,5)<"S2050"),8,IF(AND(LEFT(D2,5)>="S2060",LEFT(D2,5)<"S2065"),9,IF(AND(LEFT(D2,5)>="S2065",LEFT(D2,5)<"S2070"),10,IF(AND(LEFT(D2,5)>="S2070",LEFT(D2,5)<"S2075"),11,IF(AND(LEFT(D2,5)>="S2075",LEFT(D2,5)<"S2080"),12,IF(AND(LEFT(D2,5)>="S2090",LEFT(D2,5)<"S2105"),13,IF(AND(LEFT(D2,5)>="S2105",LEFT(D2,5)<"S2110"),14,IF(AND(LEFT(D2,5)>="S2110",LEFT(D2,5)<"S2125"),15,IF(AND(LEFT(D2,5)>="S2125",LEFT(D2,5)<"S2130"),16,IF(AND(LEFT(D2,5)>="S2130",LEFT(D2,5)<"S2135"),17,IF(AND(LEFT(D2,5)>="S2135",LEFT(D2,5)<"S2140"),18,IF(AND(LEFT(D2,5)>="S2140",LEFT(D2,5)<"S2145"),19,IF(AND(LEFT(D2,5)>="S2145",LEFT(D2,5)<"S2150"),20,IF(AND(LEFT(D2,5)>="S2150",LEFT(D2,5)<"S2160"),21,IF(AND(LEFT(D2,5)>="S2160",LEFT(D2,5)<"S2165"),22,IF(AND(LEFT(D2,5)>="S2165",LEFT(D2,5)<"S2170"),23,IF(AND(LEFT(D2,5)>="S2170",LEFT(D2,5)<"S2185"),24,IF(AND(LEFT(D2,5)>="S2185",LEFT(D2,5)<"S2200"),25,IF(AND(LEFT(D2,5)>="S2200",LEFT(D2,5)<"S2205"),26,IF(AND(LEFT(D2,5)>="S2205",LEFT(D2,5)<"S2210"),27,IF(AND(LEFT(D2,5)>="S2215",LEFT(D2,5)<"S2220"),28,IF(AND(LEFT(D2,5)>="S2200",LEFT(D2,5)<"S2225"),29,IF(AND(LEFT(D2,5)>="S2225",LEFT(D2,5)<"S2230"),30,IF(AND(LEFT(D2,5)>="S2230",LEFT(D2,5)<"S2235"),31,IF(AND(LEFT(D2,5)>="S2235",LEFT(D2,5)<"S2240"),32,IF(AND(LEFT(D2,5)>="S2240",LEFT(D2,5)<"S2260"),33,IF(AND(LEFT(D2,5)>="S2260",LEFT(D2,5)<"S2265"),34,IF(AND(LEFT(D2,5)>="S2265",LEFT(D2,5)<"S2270"),35,IF(AND(LEFT(D2,5)>="S2270",LEFT(D2,5)<"S2280"),36,IF(AND(LEFT(D2,5)>="S2280",LEFT(D2,5)<"S2285"),37,IF(AND(LEFT(D2,5)>="S2285",LEFT(D2,5)<"S2290"),38,99))))))))))))))))))))))))))))))))))))))
This is my attempt to consolidate my non-constants into an array:
=IF(AND(LEFT(D2,5)>=$Q2:$Q43,LEFT(D2,5)<$R2:$R43),$S2:$S43,99)
I moved all my greater than values, less than values, and #sortvalues, to seperate columns.
I should have known this wouldn't work and I can see why it wont, but I wanted to throw it out there anyways.
I also tried making an array using seperated formulas
{=AC$2:AC$6}
=IF(AND(LEFT(D2,5)>="S2000",LEFT(D2,5)<"S2005"),1,AC3)
=IF(AND(LEFT(D2,5)>="S2005",LEFT(D2,5)<"S2010"),2,AC4)
=IF(AND(LEFT(D2,5)>="S2010",LEFT(D2,5)<"S2015"),3,AC5)
=IF(AND(LEFT(D2,5)>="S2015",LEFT(D2,5)<"S2020"),4,AC6)
=IF(AND(LEFT(D2,5)>="S2020",LEFT(D2,5)<"S2025"),5,etc)
but when i copied/dragged it down, it would only check cell D2, instead of propagating (D2,D3,D4,ETC)
Its at this point that my brain shut down ><
Is there a way to shorten the first formula so it functions within the built in limits?
and/or
Is there a better way to do the array?
Thanks ahead of time.
Excel 2003 SP3
Bookmarks