Extract data from 1st column while skipping blanks

[gufemur]

Good morning,

I'm trying to create a sheet based off a pasted report I'll be getting daily. The report I get has a name in the first column which then shows daily hours below which start in the second column. At the end of each employee is a row that states "Total" and then has a number of different totals. The one column I need data from is the "Sold hours" column. I attached an xlsx file which has the report and I added a sheet with an example of what I'm trying to get. The problem I have is that I don't know how to have it list the names and sold hours on a sheet while not showing all the other daily values. Any help would be greatly appreciated.

Thanks
Mike

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[gufemur]

I changed it. Thanks

[Fluff13]

How about

Please Login or Register  to view this content.

[FlameRetired]

In the attached please note:

There were trailing spaces in the list of Technicians and leading spaces in the date column (" *TOTAL*"). I cleaned those up to simplify this. In the process the dates which were text "dates" were converted to actual numeric dates.

I created some Dynamic Named Ranges (DNRs) in Name Manager. If you are not familiar with DNRs they size themselves automatically to fit their data. There is a small table included that can be deleted which shows how they are defined. It looks like this:

DATE....	=Sheet1!$B$2:INDEX(Sheet1!$B:$B,ROWS(Technician)+1)
SOLD	=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH("zzzzzzz",Sheet1!$A:$A)-7)
Technician	=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzzzz",Sheet1!$A:$A)-7)

Then in A1 and copied down this formula

Please Login or Register  to view this content.

and in B1 and copied down this formula

Please Login or Register  to view this content.

[Bo_Ry]

Please try

=LET(z,Sheet1!A2:J2000,a,INDEX(z,,1),b,INDEX(z,,2),d,INDEX(z,,4),IF({1,0},FILTER(a,TRIM(a)>""),FILTER(d,b=" *Total*")))

[gufemur]

Thanks Fluff13 and Bo_Ry those are very interesting formulas. I've never heard of LET. Can you possibly give a quick rundown of that formula? I don't quite understand how it operates.

[Fluff13]

With my solution this part filters the data to just show the rows with a name in col A or Total in col B & stores that in the variable d

Please Login or Register  to view this content.

This part calculates the total number of output rows, by subtracting 2 (to remove the S1 & grand total) & then dividing by 2 & stores that in the variable r

Please Login or Register  to view this content.

This art returns all the values in column 1 of d but only from odd numbered rows

Please Login or Register  to view this content.

and the other index does much the same but takes the even numbered rows from column 4
HTH

[Fluff13]

Another (shorter) option

Please Login or Register  to view this content.

[gufemur]

Another (shorter) option

Please Login or Register  to view this content.

What does the <>"" do?

Sorry if I sound like an idiot.

[Fluff13]

It means not equal to nothing, or to put it another not blank.