Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data sorting, please can anyone advise on the best function to use?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data sorting, please can anyone advise on the best function to use?
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data sorting, please can anyone advise on the best function to use?
On May 15, 3:18 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: 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" wrote in message oups.com... 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data sorting, please can anyone advise on the best function to use?
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(xlCe llTypeVisible).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: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 "ChemicalJasper" wrote in message ups.com... On May 15, 3:18 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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" wrote in message oups.com... 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refreshing external data breaks sorting of function columns | Excel Discussion (Misc queries) | |||
Vlookup gives wrong answers when used in large data. Pls advise? | Excel Worksheet Functions | |||
Newbie: worksheet function advise (huge please) | Excel Worksheet Functions | |||
New to VLookUps.... please advise... | Excel Worksheet Functions | |||
pls advise | Excel Worksheet Functions |