Need Assistance Creating A Formula

[rdegr]

[FONT=&quot]I am having difficulty developing a formula that will pull cell data from a workbook sheet. [/FONT]
[FONT=&quot]PLEASE HELP – Looking for an Excel expert to suggest a formula.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I receive a data dump in excel (stacked data). Here is a sample of what the columns of interest look like. The each row is the inspection data of an apartment unit. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]The Apt # is always in column “J”.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Each time the data is received there is an unknown number of columns after J (K to ?). [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]There are repeating “GROUPS” of inspection data, one set of the “Bathroom + Sink” is shown below. The “groups” of data always has the same number of columns. The first group (1) is the first inspection data submitted, (2) is the second, etc. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I need a formula that COUNTS the “condition” (“Rr – Repair”) for each unit but the starting column of the Group is not know, nor is the number of Groups of data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I suspect INDEX and MATCH and/or OFFSET functions might work. I think using “Sink (?)” in the ‘find’ to identify ‘sink’ all columns might be a way.[/FONT]
[FONT=&quot]SUGGESTIONS are appreciated [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]FYI – it must be a formula (no macro .xlsm files) because the server that the file is being uploaded to will not accept .xlsm files.

[/FONT]

	J	K ~ ?	L	M	N	O	P	Q	R	S	T	U
	Apt #	K to ? Columns	Bathroom (1)	BATHROOM # (1)	Sink (1)	Condition (1)	Repair Cost $ (1)	Bathroom (2)	BATHROOM # (2)	Sink (2)	Condition (2)	Repair Cost $ (2)
1	111
2	112		Check	4	Check	Rr - Repair	25	Check	1	Check	Rr - Repair	25
3	114
4	113		Check	1	Check	Rr - Repair	25
5	124		Check	1	Check	Rr - Repair	25	Check	3	Check	Rr - Repair	25
6	123
7	122
8	121		Check	3	Check	Rr - Repair	25	Check	2	Check	Rr - Repair	25
9	131
10	132		Check	4	Check	Rr - Repair	25
11	133

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[mart37]

Put on row 2:

=COUNTIF(OFFSET($A$1,,MATCH("Condition("& ROW()-1,$1:$1,0)-1,COUNTA($J:$J),1),"Rr - Repair")

The counts for unit 1 apears.
Copy down for other units.

 

[rdegr]

Put on row 2:

=COUNTIF(OFFSET($A$1,,MATCH("Condition("& ROW()-1,$1:$1,0)-1,COUNTA($J:$J),1),"Rr - Repair")

The counts for unit 1 apears.
Copy down for other units.

Mart37, [FONT=&quot]Thank for your reply.[/FONT][FONT=&quot]I am not sure how to use your formula.

[/FONT]
[FONT=&quot]I should have been more detailed and provided greater explanation. I think the best way is to show you one of the "report" tabs that pulls from the raw data.

Below is a link to a sample workbook that will provide a better understanding.[/FONT]
[FONT=&quot]https://www.dropbox.com/s/7pi5ot7lpz8shx8/Sample%20Workbook%20-%20Report%20Tab%20and%20Data%20Tab.xlsx?dl=0[/FONT]
[FONT=&quot]You will see that the "Bedroom Interior Detail" worksheet is one of the 'report' tabs (numerous report tabs).[/FONT]
[FONT=&quot]The "Raw Export" tab contains a sample of one raw data that is received.

Hopefully this helps understand.[/FONT]
[FONT=&quot]
If just one formula can be added to the "Bedroom Interior Detail" worksheet I will be able to take it from there. Or send me a suggested formula for the "Bedroom Interior Detail" worksheet Unit # 111 (Sink Repairs) cell V:4.

[/FONT]
[FONT=&quot]Thank you so VERY MUCH again.[/FONT]
[FONT=&quot]Rob[/FONT]

 

[mart37]

I have add a row on RAW EXPORT
Dropbox - Sample Workbook - Report Tab and Data Tab (ma).xlsx