[FONT="]I am having difficulty developing a formula that will pull cell data from a workbook sheet. [/FONT]
[FONT="]PLEASE HELP – Looking for an Excel expert to suggest a formula.[/FONT]
[FONT="] [/FONT]
[FONT="]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="] [/FONT]
[FONT="]The Apt # is always in column “J”.[/FONT]
[FONT="] [/FONT]
[FONT="]Each time the data is received there is an unknown number of columns after J (K to ?). [/FONT]
[FONT="] [/FONT]
[FONT="]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="] [/FONT]
[FONT="]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="] [/FONT]
[FONT="]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="]SUGGESTIONS are appreciated [/FONT]
[FONT="] [/FONT]
[FONT="]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]
[TABLE="class: grid, width: 1210"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K ~ ?[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S [/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Apt #[/TD]
[TD="align: center"]K to ? Columns[/TD]
[TD="align: center"]Bathroom (1)[/TD]
[TD="align: center"]BATHROOM # (1)[/TD]
[TD="align: center"]Sink (1)[/TD]
[TD="align: center"]Condition (1)[/TD]
[TD="align: center"]Repair Cost $ (1)[/TD]
[TD="align: center"]Bathroom (2)[/TD]
[TD="align: center"]BATHROOM # (2)[/TD]
[TD="align: center"]Sink (2)[/TD]
[TD="align: center"]Condition (2)[/TD]
[TD="align: center"]Repair Cost $ (2)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]112[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]114[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]124[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]122[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]131[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]132[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]133[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]
[FONT="]PLEASE HELP – Looking for an Excel expert to suggest a formula.[/FONT]
[FONT="] [/FONT]
[FONT="]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="] [/FONT]
[FONT="]The Apt # is always in column “J”.[/FONT]
[FONT="] [/FONT]
[FONT="]Each time the data is received there is an unknown number of columns after J (K to ?). [/FONT]
[FONT="] [/FONT]
[FONT="]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="] [/FONT]
[FONT="]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="] [/FONT]
[FONT="]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="]SUGGESTIONS are appreciated [/FONT]
[FONT="] [/FONT]
[FONT="]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]
[TABLE="class: grid, width: 1210"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K ~ ?[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S [/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Apt #[/TD]
[TD="align: center"]K to ? Columns[/TD]
[TD="align: center"]Bathroom (1)[/TD]
[TD="align: center"]BATHROOM # (1)[/TD]
[TD="align: center"]Sink (1)[/TD]
[TD="align: center"]Condition (1)[/TD]
[TD="align: center"]Repair Cost $ (1)[/TD]
[TD="align: center"]Bathroom (2)[/TD]
[TD="align: center"]BATHROOM # (2)[/TD]
[TD="align: center"]Sink (2)[/TD]
[TD="align: center"]Condition (2)[/TD]
[TD="align: center"]Repair Cost $ (2)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]112[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]114[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]124[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]122[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]131[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]132[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Rr - Repair[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]133[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]