I have try to use pivot table to consolidate the data of the data below.
However, it is to complicate and I've lost. Any one could help me?

soure data :
"Doc:",":","1234"
"Cost","',"","Amount"
"Nil"
"Doc",":","","1235"
"Cost","","","","Amount"
"CC","","","","230.50"
"DD","","","","135"
"EE"
"Total","","","","HKD12/pc"
"Doc:",":","","1236"
"Cost","","","","Amount"
"CC","","","","220"
"EE","","","","145"
"Total","","","","USD14/pc"

Expect result
Doc CC DD EE Total
1234
1235 230.50 135 HKD12/pc
1236 220 145 USD14/pc

I wish someone could help me out. Thanks!

Hi

The data is a little messed up with single and double quotes.

I managed to make a pivot except for the unit cost (it does not like text).

The source data went like:

Code Type Details
1234 Doc: 1234
1234 Cost Amount
1234 Nil
1235 Doc: 1235
1235 Cost Amount
1235 CC 230.5
1235 DD 135
1235 EE
1235 Total HKD12/pc
1236 Doc: 1236
1236 Cost Amount
1236 CC 220
1236 EE 145
1236 Total USD14/pc

with a formula for code as
=IF(B2="Doc:",C2) (and copy it down)

You need to check the data and maybe delete some columns.


And here is the pivot table

Sum of Details Type
Code CC DD EE Total Grand Total
1234
1235 230.5 135 0 365.5
1236 220 145 0 365
G Total 450.5 135 145 0 730.5

The Total is the problem and that needs to be cleaned up.

It is probably easier to use a macro to work through the lines and clean
them that way.

Depending on your satisfaction with the above I will have a think about the
macro.

See what you think.

You could email me the worksheet and I will have a look. Click my name to
see my address.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"bondtang" wrote:

I have try to use pivot table to consolidate the data of the data below.
However, it is to complicate and I've lost. Any one could help me?

soure data :
"Doc:",":","1234"
"Cost","',"","Amount"
"Nil"
"Doc",":","","1235"
"Cost","","","","Amount"
"CC","","","","230.50"
"DD","","","","135"
"EE"
"Total","","","","HKD12/pc"
"Doc:",":","","1236"
"Cost","","","","Amount"
"CC","","","","220"
"EE","","","","145"
"Total","","","","USD14/pc"

Expect result
Doc CC DD EE Total
1234
1235 230.50 135 HKD12/pc
1236 220 145 USD14/pc

I wish someone could help me out. Thanks!