Transfering data from one spreadsheet to another.
 

I have a spreadsheet that is created by a program I use at work. I run this
report weekly and there are hundreds of loads each week. The spreadsheet has
the info I need but not in the right format. I need to know if there is a
way to pull this info into another spreadsheet in the format I am looking for
without a bunch of copying and pasteing. He is an example of what the report
looks like

Load# Destination Charge Desc. Charges
Total
12345 Columbus, OH Fuel $100
$300
12345 Columbus, OH Stop $50
$300
12345 Columbus, OH Linehaul $150
$300

It gives multiple rows of info to display the charges within the load. I
need to it to be 1 column with the charges listed across seperat columns
instead of seperate rows. This is what I need it to look like.

Load# Destination Linehual Stop Fuel
Total
12345 Columbus, OH $150 $50 $100
$300

Thanks for your help.

Transfering data from one spreadsheet to another.
 

Non-macro method.

--A-------B-------------C---------D
Load# Destination-----Desc.----Charges
12345 Columbus, OH----Fuel------$100
12345 Columbus, OH----Stop------$50
12345 Columbus, OH----Linehaul--$150

--H-----------I-----------J--------K-----L
Load#---Destination----Linehaul--Stop---Fuel
12345---Columbus, OH-----150------50-----100



H2: enter the load # (or use ADV FILTER to copy all the unique load numbers
to the H column all at once)

I2:=INDEX($B$2:$B$4, MATCH($H2, $A$2:$A$4, 0))

J2: =INDEX($D$2:$D$4, MATCH($H2&J$1, INDEX($A$2:$A$4&$C$2:$C$4, 0), 0))

Copy J2 across two columns and all load $ will appear.
Copy I2:L2 down for as many loads as you have.
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"TB@work" wrote:

I have a spreadsheet that is created by a program I use at work. I run this
report weekly and there are hundreds of loads each week. The spreadsheet has
the info I need but not in the right format. I need to know if there is a
way to pull this info into another spreadsheet in the format I am looking for
without a bunch of copying and pasteing. He is an example of what the report
looks like

Load# Destination Charge Desc. Charges
Total
12345 Columbus, OH Fuel $100
$300
12345 Columbus, OH Stop $50
$300
12345 Columbus, OH Linehaul $150
$300

It gives multiple rows of info to display the charges within the load. I
need to it to be 1 column with the charges listed across seperat columns
instead of seperate rows. This is what I need it to look like.

Load# Destination Linehual Stop Fuel
Total
12345 Columbus, OH $150 $50 $100
$300

Thanks for your help.

Transfering data from one spreadsheet to another.
 

Sorry this took so long but this is perfect. Thanks.

"JBeaucaire" wrote:

Non-macro method.

--A-------B-------------C---------D
Load# Destination-----Desc.----Charges
12345 Columbus, OH----Fuel------$100
12345 Columbus, OH----Stop------$50
12345 Columbus, OH----Linehaul--$150

--H-----------I-----------J--------K-----L
Load#---Destination----Linehaul--Stop---Fuel
12345---Columbus, OH-----150------50-----100



H2: enter the load # (or use ADV FILTER to copy all the unique load numbers
to the H column all at once)

I2:=INDEX($B$2:$B$4, MATCH($H2, $A$2:$A$4, 0))

J2: =INDEX($D$2:$D$4, MATCH($H2&J$1, INDEX($A$2:$A$4&$C$2:$C$4, 0), 0))

Copy J2 across two columns and all load $ will appear.
Copy I2:L2 down for as many loads as you have.
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"TB@work" wrote:

I have a spreadsheet that is created by a program I use at work. I run this
report weekly and there are hundreds of loads each week. The spreadsheet has
the info I need but not in the right format. I need to know if there is a
way to pull this info into another spreadsheet in the format I am looking for
without a bunch of copying and pasteing. He is an example of what the report
looks like

Load# Destination Charge Desc. Charges
Total
12345 Columbus, OH Fuel $100
$300
12345 Columbus, OH Stop $50
$300
12345 Columbus, OH Linehaul $150
$300

It gives multiple rows of info to display the charges within the load. I
need to it to be 1 column with the charges listed across seperat columns
instead of seperate rows. This is what I need it to look like.

Load# Destination Linehual Stop Fuel
Total
12345 Columbus, OH $150 $50 $100
$300

Thanks for your help.

Transfering data from one spreadsheet to another.
 

One more question. If a certain charge does not apply to a load it returns
#N/A. Is there a way to set the formula to return $0? For instance in my
example above if load 12346 does not have a fuel charge applied, I would like
the formula to return $0.

"TB@work" wrote:

Sorry this took so long but this is perfect. Thanks.

"JBeaucaire" wrote:

Non-macro method.

--A-------B-------------C---------D
Load# Destination-----Desc.----Charges
12345 Columbus, OH----Fuel------$100
12345 Columbus, OH----Stop------$50
12345 Columbus, OH----Linehaul--$150

--H-----------I-----------J--------K-----L
Load#---Destination----Linehaul--Stop---Fuel
12345---Columbus, OH-----150------50-----100



H2: enter the load # (or use ADV FILTER to copy all the unique load numbers
to the H column all at once)

I2:=INDEX($B$2:$B$4, MATCH($H2, $A$2:$A$4, 0))

J2: =INDEX($D$2:$D$4, MATCH($H2&J$1, INDEX($A$2:$A$4&$C$2:$C$4, 0), 0))

Copy J2 across two columns and all load $ will appear.
Copy I2:L2 down for as many loads as you have.
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"TB@work" wrote:

I have a spreadsheet that is created by a program I use at work. I run this
report weekly and there are hundreds of loads each week. The spreadsheet has
the info I need but not in the right format. I need to know if there is a
way to pull this info into another spreadsheet in the format I am looking for
without a bunch of copying and pasteing. He is an example of what the report
looks like

Load# Destination Charge Desc. Charges
Total
12345 Columbus, OH Fuel $100
$300
12345 Columbus, OH Stop $50
$300
12345 Columbus, OH Linehaul $150
$300

It gives multiple rows of info to display the charges within the load. I
need to it to be 1 column with the charges listed across seperat columns
instead of seperate rows. This is what I need it to look like.

Load# Destination Linehual Stop Fuel
Total
12345 Columbus, OH $150 $50 $100
$300

Thanks for your help.