TB@work
12/18/2009 2:00:00 PM
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.