Need Assistance Creating A Formula

R

rdegr

New Member
Joined
Aug 5, 2017
Messages
2
[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]
[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]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Put on row 2:
Code: 
=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.
 
Last edited:
Upvote 0
mart37 said:
Put on row 2:
Code: 
=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.
Click to expand...

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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Macro to look up and post to columns
Replies
8
Views
164
philb99
P
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
341
kvsrinivasamurthy
K
Cliffork
  • Solved
Conditional Formatting Problem
Replies
8
Views
165
DanteAmor
DanteAmor
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
228
Peter_SSs
Peter_SSs
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
404
Kaper
Kaper

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top