transfer data from sheet1 to sheet 2

[joe311]

Hi, i'm having the hardest time trying to transfer data from one sheet
to another sheet. The data that I receive comes from different sources
and are always in different order, however, the column name is always
the same. I wanted to transfer only the columns that I need to another
sheet within the same workbook in a specified order. In other words, I
want to make a template so that each time I copy and paste the data
that I received into sheet 1, I want the columns to be automatically
transferred to sheet 2 in the specified order. It doesn't matter if
its in excel formatting or vba, but if only availble in vba format,
then very clear descriptions will be greatly appreciated. I would be
very thankful for any help on this matter.

 

[Max]

Hi, i'm having the hardest time trying to transfer data from one sheet
to another sheet. The data that I receive comes from different sources
and are always in different order, however, the column name is always
the same. I wanted to transfer only the columns that I need to another
sheet within the same workbook in a specified order. In other words, I
want to make a template so that each time I copy and paste the data
that I received into sheet 1, I want the columns to be automatically
transferred to sheet 2 in the specified order. It doesn't matter if
its in excel formatting or vba, but if only availble in vba format,
then very clear descriptions will be greatly appreciated. I would be
very thankful for any help on this matter.

One play which automates it using formulas ...

Assuming source data in Sheet1, cols A to C,
col headers in row1, data from row2 down

Field1 Field2 Field3
data1 data2 data3
data11 data22 data33
data12 data23 data34
etc

And in Sheet2,

Assume the desired sequence of the col headers
placed in A1:C1 are: Field3, Field1, Field2

Put in A2:
=OFFSET(Sheet1!$A$1,ROW(A1),MATCH(A$1,Sheet1!$1:$1,0)-1)

Copy A2 to C2, fill down to say, C100,
to cover the max expected extent of data in Sheet1

For a cleaner look, suppress extraneous zeros in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

The above will return the required results in Sheet2

To refresh the data in Sheet1, clear it with the Delete key,
then copy>paste (or paste special > values) the new data

Do *not* clear by deleting the cols in Sheet1,
as this would foul up the formulas in Sheet2

---

 

[joe311]

thanks max for the help, but it's not exactly what i was looking for, i
dont think i was clear enough about what i needed. i get data sheets
from different people, they are always in a different order, but have
the same heading for each column. i wanted to pull the data from the
different people and make a separate sheet that automatically puts the
columns of data in the order i want it.

for example.
on monday i receive a data sheet with the following columns...
id# name address phone# car birthday

on tuesday i receive a data sheet with the following columns...
birthday name address car phone# id#

what i want is a template that after i input the given data would place
all the information into an order i need it to be in... such as...
id# birthday name phone# address car

 

[Guest]

thanks max for the help, but it's not exactly what i was looking for, i
dont think i was clear enough about what i needed. i get data sheets
from different people, they are always in a different order, but have
the same heading for each column. i wanted to pull the data from the
different people and make a separate sheet that automatically puts the
columns of data in the order i want it.

for example.
on monday i receive a data sheet with the following columns...
id# name address phone# car birthday

on tuesday i receive a data sheet with the following columns...
birthday name address car phone# id#

what i want is a template that after i input the given data would place
all the information into an order i need it to be in... such as...
id# birthday name phone# address car

Think my earlier interp was'nt far off,
and the suggestion should have worked ??

You just need to define the order of the column headers
you want in Sheet2, and refresh the source data in Sheet1
(I used as examples of the column headers: Field1, Field2, Field3)

Sheet2 would be the "template" that auto-returns the results
based on the source data that's pasted/refreshed into Sheet1

---

 

[joe311]

ok...got it...it works..thank you very much!

 

[Max]

ok...got it...it works..thank you very much!

Glad to hear that !
Thanks for feeding back ..

---