Excel help, copying columns

Thundyrkatz

Good Morning Everyone!

Hoping there is an easy to do this, but I can't think of one.

I get a large report from a vendor and I need to chop it down. Basically I only need a few columns from the large report but looking for the columns, which are not always in the same spot, and cutting them out or deleting the columns I don't need takes a long time.

Is there a function I can use to look for a column header in the vendors report and copy the whole column into a different report? The amount of rows in the column varies from month to month also.

I tried to use an HLookup, but I cant get it to return the column. Also, I suppose I could use a pivot table but I don't want the end report to be in a pivot table. I guess I could use the pivot table as an intermediate step, but that's cumbersome.

So, any ideas?

Dr. Frenchenstein

if you did HLookup, you'd need to have each column header you wanted as your column header in the destination file, and row numbers on the left. your formula would be =HLookup(B1, [DATA],A2,False). highlight the entire population of the vendor file for the data portion, and you should be good. you just have to be sure your headers match exactly and there are no duplicates. then you can copy down the formula for however many rows you need.

It's kind of a pain, but it should work.

Inquisitor77

There isn't going to be a way to solve for this unless there is some sort of common data that you can look for across the columns. Do the columns you need always have the same headers? Or do they use a specific data type/pattern? And do ONLY those columns conform to those patterns?

Otherwise, hunting and pecking is probably your best solution. Instead of deleting columns, I'd recommend copy/pasting them immediately into a different sheet. That might save you most of your time spent waiting for Excel to delete the column.

Daimar

As the doctor says, HLOOKUP should be all you need, if you're not getting anything returned it is most likely because you are not entering the customer's column header exactly the same or it is a text string you don't have in quotation marks. I've seen a lot of custom reports that spit out weird headers that have a dozen extra spaces that you can miss if you don't pay really close attention.

The best way to get around this is the first time you set it up copy and paste the column headers you want and reference them like in the formula above (except with $B$1 so that the reference doesn't change as you copy it down), as long as they don't change after that you can continue to use the same sheet, just re-jig the data field it references or paste the new data into the customer data tab. If you're just typing them in you will need to put text fields in quotes like so: =HLOOKUP("datacolumn",A:F,M1,FALSE) where M1 is a column from 1 to a thousand or however long the data column is.

Thundyrkatz

OK, so i gave it a try and it sort of works.

Inquisitor77... The column headers are the same from month to month, but they are not always in the same spot. (Always in row 1, but maybe column B this month and column D next month and then back to column B the 3rd month)

So i am saving the report down into one file and then using a different file t create the report i need. When i type in my HLookup formula...

=HLOOKUP(A$1,'[Vendor Data File.xlsx]Data Tab'!$A$1:$DZ$1295,2,FALSE)

It returns the data i want for that cell (YAY!) but if i copy it down i get the same because the 2 before the False above does not change to a 3 and so on (BOO).

If i but in a cell reference to the vendor data file in A2 it craps out.

how do i get it to populate down the rows?

Dis'

Use

=HLOOKUP(A$1,'[Vendor Data File.xlsx]Data Tab'!$A$1:$DZ$1295,ROW(),FALSE)

ROW() will give the row number of your target sheets cell, so will increment when you fill-down

Thundyrkatz

BOOM! that worked! Nice Dis'

I think i am business now, thanks for the help everyone!

Dr. Frenchenstein

dis gave a less dumb version of what i was saying (where column A is row numbers), i learned something!

Thundyrkatz

Every once in a while I learn a new trick in excel and I can never tell anyone how excited I am about my new trick. Almost no one cares about Excel, so I appreciate having you guys around to help!