I have a worksheet that contains hundreds of columns. Each group of three are
related to each other (Cols A-C, D-F, etc...).
I have this formula that works well for columns A-C

=IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color
Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B $1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=CO LUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B $2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COL UMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B $2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A $3=$A$2,$B$2+1,1),IF(AND(COLUMN()=COLUMN($C$3),ROW ()=3),IF($A$3=$A$4,"",IF($A$3=$A$2,$B$3,1)),IF(COL UMN()=COLUMN(B2),IF(A2=A1,B1+1,1),IF(COLUMN()=COLU MN(C2),IF(A2=A3,"",IF(A2=A1,B2,1)))))))))))

I know it is rather thick and probably difficult to read through, but that
is my formula and it works exactly how I want......sort of.
My problem is two-fold and I hope I say it correctly....

Column A contains a number (ex. 351), Column B counts the number of
occurrences each particular instances is. Column C shows the last number of
that occurrence. (Will provide example at the bottom)

One, I need to be able to put this in cell A1 and copy it over and down to a
variable number of columns and rows. Currently, I have to copy it exactly as
shown above (not drag/copy) into cell B2, C2, B3, and C3, then I can do a
drag copy of B3:C3 all the way down to the bottom of my data.

Two, I can't use it in column A or in Row 1. (the cell references to col A
and to row 1 causes #REF!). But I need it to know to look in cell A1 (or D1,
or G1, etc...)

My ultimate goal really is to just have one formula that I can put into cell
A1 and can drag copy from top to bottom, left to right, without any manual
changes.

Worksheet example:

Column A Column B Column C
722 1 1
351 1
351 2
351 3 3
879 1
879 2 2
151 1 1
744 1 1

Columns D-F would look similar but would have different numbers (Col A) in a
different order. They are unrelated to Columns A-C. They are their own group
of three, as would be G-I and so on.

Disregard, I have found a solution.
For those who are curious, this is my new formula, which I am able to put
into cell A1 and drag copy all the way over and down through my entire sheet.
It is still a little messy and I plan to go back and see about simplifying
it, but for now, it works....

=IF(NOT(ISBLANK('Page 5 Counts'!A1)),VLOOKUP('Page 5 Counts'!A1,'Color
Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=IF(ISNA(V LOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),ROW()=1),IF(INDIRECT("R 2C"&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE)=INDIRECT("R1C"&VL OOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE),"",1),IF(AND(COLU MN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),ROW()=1),IF(NOT(INDIREC T("R2C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)=INDIRECT("R1C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)),INDIRECT("R1C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE),FALSE),""),IF(AND(COLUMN ()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),ROW()=2),IF(INDIRECT("R 2C"&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE)=INDIRECT("R1C"&VL OOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE),2,1),IF(AND(COLUM N()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),ROW()=2),IF(NOT(INDIREC T("R2C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)=INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)),INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE),FALSE),""),IF(AND(COLUMN ()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),ROW()=3),IF(INDIRECT("R 3C"&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE)=INDIRECT("R2C"&VL OOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE),SUM(INDIRECT("R2C "&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,1,FALSE),FALSE),1),1),IF(AND(COLU MN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),ROW()=3),IF(NOT(INDIREC T("R3C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)=INDIRECT("R4C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)),IF(INDIRECT("R3C"&VLOOKUP(COLUMN(),'Colo r
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)=INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE),INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE),FALSE),1),""),IF(AND(COL UMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$E$1:$E$100,1,FALSE)),ROW()3),IF(INDIRECT("R "&ROW()&"C"&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE)=INDIRECT("R"&ROW( )-1&"C"&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,2,FALSE),FALSE),SUM(INDIRECT("R"& ROW()-1&"C"&VLOOKUP(COLUMN(),'Color
Key'!$E$1:$F$100,1,FALSE),FALSE),1),1),IF(AND(COLU MN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),ROW()3),IF(INDIRECT("R "&ROW()&"C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)=INDIRECT("R"&ROW()+1&"C"&VLOOKUP(COLUMN() ,'Color Key'!$G$1:$H$100,2,FALSE)-1,FALSE),"",
IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,1,FALSE)),ROW()3),IF(INDIRECT("R "&ROW()&"C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE)=INDIRECT("R"&ROW()-1&"C"&VLOOKUP(COLUMN(),'Color
Key'!$G$1:$H$100,2,FALSE)-1,FALSE),INDIRECT("R"&ROW()&"C"&VLOOKUP(COLUMN(),' Color Key'!$G$1:$H$100,1,FALSE)-1,FALSE),1))))))))))))



"Kalffiend" wrote:

I have a worksheet that contains hundreds of columns. Each group of three are
related to each other (Cols A-C, D-F, etc...).
I have this formula that works well for columns A-C

=IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color
Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B $1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=CO LUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B $2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COL UMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B $2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A $3=$A$2,$B$2+1,1),IF(AND(COLUMN()=COLUMN($C$3),ROW ()=3),IF($A$3=$A$4,"",IF($A$3=$A$2,$B$3,1)),IF(COL UMN()=COLUMN(B2),IF(A2=A1,B1+1,1),IF(COLUMN()=COLU MN(C2),IF(A2=A3,"",IF(A2=A1,B2,1)))))))))))

I know it is rather thick and probably difficult to read through, but that
is my formula and it works exactly how I want......sort of.
My problem is two-fold and I hope I say it correctly....

Column A contains a number (ex. 351), Column B counts the number of
occurrences each particular instances is. Column C shows the last number of
that occurrence. (Will provide example at the bottom)

One, I need to be able to put this in cell A1 and copy it over and down to a
variable number of columns and rows. Currently, I have to copy it exactly as
shown above (not drag/copy) into cell B2, C2, B3, and C3, then I can do a
drag copy of B3:C3 all the way down to the bottom of my data.

Two, I can't use it in column A or in Row 1. (the cell references to col A
and to row 1 causes #REF!). But I need it to know to look in cell A1 (or D1,
or G1, etc...)

My ultimate goal really is to just have one formula that I can put into cell
A1 and can drag copy from top to bottom, left to right, without any manual
changes.

Worksheet example:

Column A Column B Column C
722 1 1
351 1
351 2
351 3 3
879 1
879 2 2
151 1 1
744 1 1

Columns D-F would look similar but would have different numbers (Col A) in a
different order. They are unrelated to Columns A-C. They are their own group
of three, as would be G-I and so on.