software

Hi

I need to analyze some shipment data that is not currently in a format
suitable for analysis.

My shipment data consists of a shipment no., basic freight charge, fuel
surcharge, customs fee etc. The problem is that each charge is specified
on a separate line. This means that data about each shipment is covering
several lines in my spreadsheet. To sum up column A contains shipment
no. Column B: Charge type. Column C: Charge cost.

I would like the data to be structured so each shipment covers only one
row, and each individual charge type has its own column.

Any ideas on how to achieve this?--
po2206
------------------------------------------------------------------------
po2206's Profile: www.excelforum.com/member.php...oamp;userid=32344
View this thread: www.excelforum.com/showthread...hreadid=521060The following example might help:
cjoint.com/?dkpSQR4ql5

The formula in sheet2 is:
=OFFSET(Feuil1!R1C1,(ROW()-1)*4 (COLUMN()-1),0)

HTH
--
AP

quot;po2206quot; gt; a écrit dans
le message de news
gt;
gt; Hi
gt;
gt; I need to analyze some shipment data that is not currently in a format
gt; suitable for analysis.
gt;
gt; My shipment data consists of a shipment no., basic freight charge, fuel
gt; surcharge, customs fee etc. The problem is that each charge is specified
gt; on a separate line. This means that data about each shipment is covering
gt; several lines in my spreadsheet. To sum up column A contains shipment
gt; no. Column B: Charge type. Column C: Charge cost.
gt;
gt; I would like the data to be structured so each shipment covers only one
gt; row, and each individual charge type has its own column.
gt;
gt; Any ideas on how to achieve this?
gt;
gt;
gt; --
gt; po2206
gt; ------------------------------------------------------------------------
gt; po2206's Profile:
www.excelforum.com/member.php...oamp;userid=32344
gt; View this thread: www.excelforum.com/showthread...hreadid=521060
gt;

Thanks.
Unfortunately the charges for each shipment are not standard. Therefore
one shipment might only have one charge, while others could have three
or four (and take up three or four rows).

That rules out your suggestion if I understand it correctly.--
po2206
------------------------------------------------------------------------
po2206's Profile: www.excelforum.com/member.php...oamp;userid=32344
View this thread: www.excelforum.com/showthread...hreadid=521060Is there some way you can tell the first line of a shipment from others?

Please post some example.

HTH
--
AP

quot;po2206quot; gt; a écrit dans
le message de news
gt;
gt; Thanks.
gt; Unfortunately the charges for each shipment are not standard. Therefore
gt; one shipment might only have one charge, while others could have three
gt; or four (and take up three or four rows).
gt;
gt; That rules out your suggestion if I understand it correctly.
gt;
gt;
gt; --
gt; po2206
gt; ------------------------------------------------------------------------
gt; po2206's Profile:
www.excelforum.com/member.php...oamp;userid=32344
gt; View this thread: www.excelforum.com/showthread...hreadid=521060
gt;

The first line of each shipment is characterised by a new shipment no.
Each shipment no. is unique.

The three columns of data look like this:

SHIPMENT NO. COST TYPE CHARGE
2143214432 Freight 23
2143214432 Fuel charge 2
2143214432 Customs fee 10
4342342342 Freight 43
4342342342 Fuel charge 4
8734343254 Freight 62
8734343254 Fuel charge 5--
po2206
------------------------------------------------------------------------
po2206's Profile: www.excelforum.com/member.php...oamp;userid=32344
View this thread: www.excelforum.com/showthread...hreadid=521060Copy the following code into a Module.
Adjust worksheets names in Const lines (at the beginning)

I split the code into 2 macros:
1) CreateDestWS: creates the dest worksheet and populates column headers
2) MoveData : mouve data from source to dest WS

If anything goes wrong, please post back.

HTH
--
AP

'------------------------------------------
Const srcWsName As String = quot;Sheet1quot;
Const destWsName As String = quot;Sheet2quot;

Sub createDestWS()
Dim rngSrcHeaders As Range
Dim rngDestHeaders As Range
Dim strColAHeader As String
With Worksheets(srcWsName)
strColAHeader = .Range(quot;A1quot;).Value
Set rngSrcHeaders = .Range( _
.Range(quot;B1quot;), _
.Cells(Rows.Count, quot;Bquot;).End(xlUp) _
)
End With
' Create dest WS if does not exist
On Error Resume Next
If Worksheets(destWsName) Is Nothing Then
With Worksheets.Add(after:=Worksheets(Worksheets.Count) )
.Name = destWsName
End With
End If
On Error GoTo 0

With Worksheets(destWsName)
' Clear contents if any
.UsedRange.ClearContents
' Filter out uique values for col headers
rngSrcHeaders.AdvancedFilter _
action:=xlFilterCopy, _
copytorange:=.Range(quot;A1quot;), _
unique:=True
' Traspose vertical list into horizontal Col headers
Set rngDestHeaders = .Range( _
quot;A2quot;, _
.Cells(Rows.Count, quot;Aquot;).End(xlUp) _
)
rngDestHeaders.Copy
.Range(quot;B1quot;).PasteSpecial _
Paste:=xlPasteValues, _
operation:=xlNone, _
Transpose:=True
rngDestHeaders.ClearContents
' Set header for col A
.Range(quot;A1quot;).Value = strColAHeader
End With
End Sub

Sub moveData()
Dim srcRng As Range
Dim destRng As Range
Dim headersRng As Range
Dim iCol As Long

' Initialize
Set srcRng = Worksheets(srcWsName).Range(quot;A2quot;)
With Worksheets(destWsName)
Set destRng = .Range(quot;A1quot;)
Set headersRng = .Range( _
quot;B1quot;, _
.Cells(1, Columns.Count).End(xlToRight) _
)
End With
Do While srcRng.Value lt;gt; quot;quot;
If srcRng.Value lt;gt; srcRng.Offset(-1, 0).Value Then
Set destRng = destRng.Offset(1, 0)
' Copy shipment no.
destRng.Value = srcRng.Value
End If
' Search source charge type thru dest headers
iCol = Application.WorksheetFunction.Match( _
srcRng.Offset(0, 1).Value, _
headersRng, _
0)
' Copy source charge value into dest column
destRng.Offset(0, iCol).Value = _
srcRng.Offset(0, 2)
'End of loop: Skip to next source row
Set srcRng = srcRng.Offset(1, 0)
Loop
End Sub
'----------------------------------------------------------------
quot;po2206quot; gt; a écrit dans
le message de news
gt;
gt; The first line of each shipment is characterised by a new shipment no.
gt; Each shipment no. is unique.
gt;
gt; The three columns of data look like this:
gt;
gt; SHIPMENT NO. COST TYPE CHARGE
gt; 2143214432 Freight 23
gt; 2143214432 Fuel charge 2
gt; 2143214432 Customs fee 10
gt; 4342342342 Freight 43
gt; 4342342342 Fuel charge 4
gt; 8734343254 Freight 62
gt; 8734343254 Fuel charge 5
gt;
gt;
gt; --
gt; po2206
gt; ------------------------------------------------------------------------
gt; po2206's Profile:
www.excelforum.com/member.php...oamp;userid=32344
gt; View this thread: www.excelforum.com/showthread...hreadid=521060
gt;

Brilliant! It works and is exactly what I was looking for.
Thanks a lot :-)--
po2206
------------------------------------------------------------------------
po2206's Profile: www.excelforum.com/member.php...oamp;userid=32344
View this thread: www.excelforum.com/showthread...hreadid=521060Thanks for the feedback.

Cheers,
--
AP

quot;po2206quot; gt; a écrit dans
le message de news
gt;
gt; Brilliant! It works and is exactly what I was looking for.
gt; Thanks a lot :-)
gt;
gt;
gt; --
gt; po2206
gt; ------------------------------------------------------------------------
gt; po2206's Profile:
www.excelforum.com/member.php...oamp;userid=32344
gt; View this thread: www.excelforum.com/showthread...hreadid=521060
gt;