Pulling information from a generated report

[emoakstreet]

I have a report that is automatically generated once a day.

It arrives as an Excel file with Groupings (by rows) and merged cells. I would like to create a worksheet that pulls the data from this report for anything over a certain price.

The columns are:
[TABLE="width: 480"]
<tbody>[TR]
[TD]Job Stat[/TD]
[TD]Job No[/TD]
[TD]Cust No[/TD]
[TD]Cust Name[/TD]
[TD]Site No Price[/TD]
[/TR]
</tbody>[/TABLE]

The above is Row 3 in the sheet. Job Stat (Column A) has the merged cells where the Status only appears at the top row of the grouping. In other words, for the Job Stat of "Assigned", this only shows in Row 3. There are then 20 jobs listed with Column A blank. There is a totals line at the bottom of each group. Then A25 will have "Completed". Followed by a number of jobs with Column A blank until the start of the next status. And so on through about 10 different statuses.

What I would like is a worksheet that has a box where I put in a price and it then pulls all the relevant jobs (including Job Status) that are greater than or equal to that price.

Please let me know if I need to provide any more information.

Thanks in advance!

 

[stunnrock]

Hi, this is easier (and much more efficient) with a helper column. For this example I have the data in Sheet1!F4:F50. In the second sheet, you would enter the price in B1, and have your column headings in B2:G2. Column A could be hidden, or formatted to distinguish it from the data.

In A1:

=ROWS(Sheet1!$F$4:$F$50)

In A2:

=COUNTIF(Sheet1!$F$4:$F$50,">"&$B$1)

In A3:

=IF(ROWS(A$3:A3)>A$2,"-",SMALL(INDEX(ROW(Sheet1!$F$4:$F$50)-((Sheet1!$F$4:$F$50>$B$1)*$A$1),),ROWS(A$3:A3))+$A$1-ROW(Sheet1!$F$4)+1)

In B3:

=IF($A3="-","-",INDEX(Sheet1!$A$4:$A$50,MAX(INDEX(ROW(Sheet1!$A$4:INDEX(Sheet1!$A$4:$A$50,$A3))*(Sheet1!$A$4:INDEX(Sheet1!$A$4:$A$50,$A3)<>""),))-ROW(Sheet1!$F$4)+1))

In C3:

=IF($A3="-","-",IF(INDEX(Sheet1!B$4:B$50,$A3)="","",INDEX(Sheet1!B$4:B$50,$A3)))

Copy C3 along D3:G3, then copy A3:G3 downward as far as required.

 

[stunnrock]

To skip Total rows, change A2 to:

=COUNTIF(Sheet1!$F$4:$F$50,">"&$B$1)-COUNTIF(Sheet1!$A$4:$A$50,"Total")

and A3 to:

=IF(ROWS(A$3:A3)>A$2,"-",SMALL(INDEX(ROW(Sheet1!$F$4:$F$50)-(((Sheet1!$F$4:$F$50>$B$1)-(Sheet1!$A$4:$A$50="Total"))*$A$1),),ROWS(A$3:A3))+$A$1-ROW(Sheet1!$F$4)+1)

copy A3 downward.

 

[emoakstreet]

This works great for pulling everything over. Thanks for that.

However, the Totals line is not labeled. There is a count that shows up in the Job No column (Column B). If possible, can use that number because it will be less than 100 and a job number is 8 digits long?

 

[stunnrock]

Okay, A2 becomes:

=COUNTIF(Sheet1!$F$4:$F$50,">"&$B$1)-COUNTIF(Sheet1!$B$4:$B$50,"<"&100)

and A3 becomes:

=IF(ROWS(A$3:A3)>A$2,"-",SMALL(INDEX(ROW(Sheet1!$F$4:$F$50)-(((Sheet1!$F$4:$F$50>$B$1)-(Sheet1!$B$4:$B$50<100))*$A$1),),ROWS(A$3:A3))+$A$1-ROW(Sheet1!$F$4)+1)