Is it possible to check a range for a criteria... but only up to a certain cell in the range?

[jvs411]

Have a really weird problem to solve here. I have a table here with dates (may not be in consistent chronological order) with multiple of the same weeks in a column. As you can see below

Because of this, I'm trying to find a way to auto-label each column with a unique Week label in Row 1, so it'll be like "Wk1a" and if there's a second Wk1, it'll be "Wk1b" and so on...
So the end result should be like this:

I realize in order to do this though, I'll need the formula in each cell in Row 1 to be able to detect all previous cells in the same row its in to know how to label with the letter extensions a, b, c, d, etc...
So for example, cell E2 needs be able detect B2:D2 before it in order to decide what is the letter extension for its own week. Cell F2 needs be able detect B2:E2. Cell G2 needs be able detect B2:F2. And so on.

A second bigger problem is that I have multiple tables which aren't aligned in the same single row. So the formula for each cell needs able to detect not only all previous cells in the same row its in, but also be able detect all cells in the previous row too.
For example, cell C5 needs detect not only row 2:2 up to its own cell (in this case only B5), but also the entire row A1:H1.

Ultimately, the whole end result will be like this:

Anyone knows a solution? I'd be so grateful... :')

 

[jvs411]

Sorry I realized there's a typo in the cell references in my post. Corrected:

So for example, cell E1 needs be able detect B1:D1 before it in order to decide what is the letter extension for its own week. Cell F1 needs be able detect B1:E1. Cell G1 needs be able detect B1:F1. And so on.

For example, cell C5 needs detect not only row 5:5 up to its own cell (in this case only B5), but also the entire row A1:H1.

 

[Anonymous1378]

Try something like this.

Book1
	A	B	C	D	E	F	G	H
1		Wk1a	Wk2a	Wk2b	Wk3a	Wk2c	Wk4a	Wk4b
2	Week	1	2	2	3	2	4	4
3
4
5		Wk5a	Wk6a	Wk6b	Wk6c	Wk7a	Wk8a	Wk8b
6	Week	5	6	6	6	7	8	8
7
8
9		Wk9a	Wk10a	Wk9b	Wk12a	Wk11a	Wk12b	Wk13a
10	Week	9	10	9	12	11	12	13
11
12
13		Wk13b	Wk14a	Wk15a	Wk14b	Wk14c	Wk16a	Wk17a
14	Week	13	14	15	14	14	16	17
15
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	="Wk"&B2&CHAR(96+COUNTIF($B2:B2,B2))
B5:H5,B13:H13,B9:H9	B5	="Wk"&B6&CHAR(96+COUNTIF($B6:B6,B6)+COUNTIF($B2:$H4,B6))

 

[jvs411]

Thanks so much! It worked. However I noticed a weird error here when I extended the row as a test duplicate

You can see in B17, it also says Wk13b which is a repeat from B13

 

[Anonymous1378]

COUNTIF($B2:$H4,B6))

Thanks so much! It worked. However I noticed a weird error here when I extended the row as a test duplicate
View attachment 100285

You can see in B17, it also says Wk13b which is a repeat from B13

COUNTIF($B2:$H4,B6)) should be COUNTIF($B$2:$H4,B6)) instead, an absolute row reference was missing.

 

[jvs411]

Was there a typo in your msg? But anyway I figured it out. By absolute ref, you mean sticking the beginning cell ref at the top right?
I changed $B2 to $B$2 and now it works

All previous rows are highlighted.

I guess the only limitation to this is that I cannot insert any numbers in any of the rows that isn't the Week row or the COUNTIF will count extras..

 

[Anonymous1378]

Was there a typo in your msg? But anyway I figured it out. By absolute ref, you mean sticking the beginning cell ref at the top right?
I changed $B2 to $B$2 and now it works

All previous rows are highlighted.

I guess the only limitation to this is that I cannot insert any numbers in any of the rows that isn't the Week row or the COUNTIF will count extras..

View attachment 100286

If your regular usage will involve inserting whole numbers in between those rows, further functions (replacing COUNTIF with SUM(IF()), and using FILTER()) can be used to only get data from rows with "Week" in column A; is that something you would need?

 

[jvs411]

If your regular usage will involve inserting whole numbers in between those rows, further functions (replacing COUNTIF with SUM(IF()), and using FILTER()) can be used to only get data from rows with "Week" in column A; is that something you would need?

If it makes the formula more accurate, sure. How would that work?