Copy only Populated Cells from one sheet to another

[bigz60]

Hello,

I have one sheet that consists of four columns, and many rows.

The first column has my different locations, and each location is listed only once. The second column contains daily sales for the period. Each row is has a value in the second column. The third column has a sum of the sales for each location, and is located on the same row as the location. The final column has simply a sequential number, starting at 0 and incrementing up by 1 each row.

I would like to take the value of the sequential numbers for only the rows that contain a location name in the first column, and populate a range of cells on a separate sheet(tab). However, I would like to skip any blanks.

Here is an example

Location 1 15 45 0
10 1
15 2
5 3
Location 2 10 25 4
15 5

Should return the following:

0
4

I hope this makes sense, and thank you in advance.

 

[SkipVought]

I'd use MS Query, with a criteria, Location Is Not Null.

I assume that your data has headings as all tabular data should.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bigz60]

Skip,

The data does have headings.

Please forgive my ignorance, but how do I use MS Query in excel? I realize that I forgot to mention in my original post that I am working in excel.

 

[SkipVought]

I assumed Excel.

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You could also use the PivotTable Wizard and deselect (blanks) in the PT.

You could also use the AutoFilter, copy the rows and paste special - VALUES into the other sheet, if it's a one-time deal.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]