Reformatting report based on a substring within a cell

[Guest]

Hello,

I am looking for some help with a spreadsheet that was given to me, that needs to be reformatted based on the occurence of a substring within a cell.

Actually, the substring will be a keyword within a comments cell within a row. Unfortunately the comments are arbitrarily entered. But based on the occurence of the substring (e.g. truck, air, sea), the new report with cost associated figures that are part of the same row.

An abbreviated sample follows:

Shipments: Apparel
Lane: Pacific
PO Nbr Cost Date Incurred Reason
E76544 889.00 7/13/04 Air shipped due to rush
A88907 567.50 7/01/04 Customer Request delivery by truck
B86521 952.75 7/06/04 On the sea per contract


The reformatted report should be:

Reason Mode
Air Land Sea
Rush 889.00
Cust Req 567.50
Contract 952.75

Totals 889.00 567.50 925.75

The flow should include:
1. Select only the rows with the neccessary data based on the substring.
2. Group them by reason.
3. Create the reformatted report.

Thanks in advance.

Sonny

 

[CLR]

Hi.........

Somehow or other, you're gonna have to come up with a column that defines
the mode of transportation that you wish to categorize......Air, Land, and
Sea.........in your sample data you want "truck" to be assigned the "Land"
category.........we don't know how many other combinations might be on your
real data, such as boat=sea, ship=sea, rail=land, train=land, plane=air, etc
etc........

The words in the "Reason" column can be easily broken into separate columns
with the Data > Text to columns, using space as a delimiter
feature...........but you need to define a list of what=what for your
separations.........then someone can write the formulas to create the
columns you're looking for.........



hth
Vaya con Dios,
Chuck, CABGx3

Hello,

I am looking for some help with a spreadsheet that was given to me, that

needs to be reformatted based on the occurence of a substring within a cell.

Actually, the substring will be a keyword within a comments cell within a

row. Unfortunately the comments are arbitrarily entered. But based on the
occurence of the substring (e.g. truck, air, sea), the new report with cost
associated figures that are part of the same row.