extract certain columns based on title

[Griffin29]

Hi All, I am confusing myself and struggling with something I think should be simple. Please help

I have a ws with approx 30 columns, they are always named the same but sometimes there are only 25 of them and sometimes they are in a different order, it is really annoying.
In one of the columns is a unique number, another contains a geography - the remaining are just additional data some I need some I don't.

I would like to be able to set up a system where I paste the data into a sheet in a different workbook (let's call it TEMPDATA) and then in populates a pre-formated table in another shhet (let's call it DATA) with specific columns in a specific order. So for example:

Supplied data column headings are: A B D H C F E in that order. I will paste that as raw data into a worksheet TEMPDATA
I want sheet DATA to have headings A B C and F only but to populate with the 'new data' from TEMPDATA

I've tried some vlookup and match/index but it is far from efficient. I've also tried recording a macro that re-arranges the columns after pasting and deletes the ones I don't need but because the order changes it often just ends up deleting things I do need. I think I need something that says 'Look for a heading in TEMPDATA row 1 called "A" and if you find it paste from row 2 down to the end into DATA under the "A" heading then to keep going for data in BCand F.

Help appreciated - maybe I am overthinking but spent the morning achieving little.

 

[dmt32]

Hi,

You Should be able to do what you want using advanced filter either manually or with a macro

Manually

1 – add sheet (Data)

2 -From your sheet (TempData) copy the headings in order required to sheet Data - its important these headings are the same

3 – with sheet Data Active, form Ribbon Data > Sort & filter > Advanced

4 – From Displayed Dialog select

• Copy To Another Location
• List Range – Select the ENTIRE Range in your Sheet TempData (including the headers)
• Copy To – place cursor in the field takes you back to Sheet Data. Select The Headers

Press ok should filter all the records

with a macro

Sub filterdata()

    Sheets("TempData").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Data").Range("A1:D1"), Unique:=False
End Sub

The macro assumes your sheet Data already exists with required Headings & both sheets are named TempData & Data accordingly.

Hope Helpful


Dave

 

[Griffin29]

Hi,

You Should be able to do what you want using advanced filter either manually or with a macro

Manually

1 – add sheet (Data)

2 -From your sheet (TempData) copy the headings in order required to sheet Data - its important these headings are the same

3 – with sheet Data Active, form Ribbon Data > Sort & filter > Advanced

4 – From Displayed Dialog select

• Copy To Another Location
• List Range – Select the ENTIRE Range in your Sheet TempData (including the headers)
• Copy To – place cursor in the field takes you back to Sheet Data. Select The Headers

Press ok should filter all the records

with a macro

Sub filterdata()

    Sheets("TempData").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Data").Range("A1:D1"), Unique:=False
End Sub

The macro assumes your sheet Data already exists with required Headings & both sheets are named TempData & Data accordingly.

Hope Helpful


Dave

Dave ........you have blown my mind, that is so helpful thank you. Really appreciate the manual explanation too. I looked at it, thought 'not a chance I must have explained what I wantd incorrectly' but it worked like a charm.
Can I be really cheeky, rather than me do another step once I have the data, is there a way I could incorporate an IF statement or extra filter so for example if the data in column A was all Names, I would choose only the Rows with Tim in ?

 

[dmt32]

Hi,
Advanced filter is often overlooked for such requirements

To do what you want you just include a filter criteria

- Copy required heading (Column A) from TempData sheet to F1 in Data Sheet
- Enter the Name You want to Filter In F2

The run this updated macro

Sub filterdata()

    Sheets("TempData").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                                CriteriaRange:=Sheets("Data").Range("F1:F2"), _
                                                CopyToRange:=Sheets("Data").Range("A1:D1"), Unique:=False
End Sub

This will filter all records that match both whole & part names
If you need to filter an exact match then in F2 you need to enter criteria as follows

="=Tim"

To filter ALL records leave F2 blank

Dave

 

[Griffin29]

Hi,
Advanced filter is often overlooked for such requirements

To do what you want you just include a filter criteria

- Copy required heading (Column A) from TempData sheet to F1 in Data Sheet
- Enter the Name You want to Filter In F2

The run this updated macro

Sub filterdata()

    Sheets("TempData").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                                CriteriaRange:=Sheets("Data").Range("F1:F2"), _
                                                CopyToRange:=Sheets("Data").Range("A1:D1"), Unique:=False
End Sub

This will filter all records that match both whole & part names
If you need to filter an exact match then in F2 you need to enter criteria as follows

="=Tim"

To filter ALL records leave F2 blank

Dave

You have opened my eyes, thank you again, really helpful and I am already looking at the possibilities in other worksheets I need to use.

 

[dmt32]

You have opened my eyes, thank you again, really helpful and I am already looking at the possibilities in other worksheets I need to use.

AdavancedFilter Method is a very powerful function - you can apply a number of filters & extract data required with very little code.

Glad all worked ok for you

Dave