Data sorting, please can anyone advise on the best function to use?

[ChemicalJasper]

Dear Learned Users,

I have a problem with data sorting, please can anyone advise on the
best function to use?

I have a sheet with some 5500 rows of data (across 40+ colums) of
which I cannot alter the format.

I have a second sheet with some 150 rows of data

On a third sheet I have a combo box using the second sheet as the
list. I want to be able to select a record from the list (from the
second sheet) and populate a report/list of records from the first
sheet which match the data selected from the drop down.

The data in the first sheet that may match is in column 'M' and I need
to populate the report sheet with data from 'B' to 'AT' of the first
sheet.

Is there a data function within Excel that can do this or will it have
to be VB coded?

Thanking you

Steve

 

[Bernie Deitrick]

Steve,

The best thing to do is to use a data filter on column M of the first sheet, and select the value
from the drop down at the top of that column. That way, you won't need to use a third sheet at all.

HTH,
Bernie
MS Excel MVP

 

[ChemicalJasper]

Steve,

The best thing to do is to use a data filter on column M of the first sheet, and select the value
from the drop down at the top of that column. That way, you won't need to use a third sheet at all.

HTH,
Bernie
MS Excel MVP














- Show quoted text -

Thanks for the sugestion Bernie,

I already have filters on the first sheet, however I want to be able
to pull the data out (selected data colums) into a third report sheet
and then be able to do further manupilation on the data with out
affecting the primary records.

Thanking you

Steve

 

[Bernie Deitrick]

Steve,

Then how about a macro? After filtering the sheet "All Data" for the desired values, this macro
will copy the visible cells to the sheet "Selected Data" (after clearing it):

Sub TransferAllData()
Worksheets("Selected Data").UsedRange.ClearContents
Worksheets("All Data").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Selected Data").Range("A1")
End Sub

Or, if you know which columns you want: (I hope you can see the pattern....)

Sub TransferSomeData()
Worksheets("Selected Data").UsedRange.ClearContents
Worksheets("All Data").Range("A:A").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Selected Data").Range("A:A")
Worksheets("All Data").Range("D").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Selected Data").Range("B:B")
Worksheets("All Data").Range("L:L").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Selected Data").Range("C:C")
End Sub


HTH,
Bernie
MS Excel MVP