Convert Data from Code/Amount Set to Proper Record

RGottesman · Dec 6, 2016

I need to convert data received from the system that is in the following format

ID	Code	Amount	code2	amount2	code3	amount3	code4	amount4
123	REG	40	VAC	24	PTO	16
456	O/T	10	REG	72	VAC	8

To a more usable format of either

id	code	amount
123	REG	40
123	VAC	24
123	PTO	16
456	O/T	10
456	REG	72
456	VAC	8

Or a table by code

ID	REG	VAC	PTO	O/T
123	40	24	16
456	72	8		10

Any suggestions will be greatly appreciated, thank you in advance

Canapone · Dec 7, 2016

Hi,

I'd need to know what version of Excel are you using and if there is MAX number of amounts to deal:more or less.

Regards

Canapone · Dec 7, 2016

Hi again ,

...there is MAX number of amounts per Id to deal .

Sorry if my English is not clear.

RGottesman · Dec 7, 2016

In this file, there are up to 50 sets of codes and amounts

Canapone · Dec 7, 2016

Hi again,

thanks, 50 sets per ID means a database with 150 columns.

I'd need to know what version are you using, I would like to use AGGREGATE (available from Excel 2010 and newer versions).

Regards

RGottesman · Dec 7, 2016

Canapone · Dec 7, 2016

Hi

in the attachment DATA in A1:I1000

Formulas to be copied down

in M2

Code:

=iferror(index($a$2:$a$1000,match(aggregate(15,6,subtotal(2,offset($b$2,,,row($2:$1000)-1,columns($b:$i)))/(subtotal(2,offset($b$2,,,row($2:$1000)-1,columns($b:$i)))>=rows(a$1:a1)),1),index(subtotal(2,offset($b$2,,,row($2:$1000)-1,columns(b:i))),),0)),"")

in N2

Code:

=iferror(index($a$2:$i$1000,match(m2,$a$2:$a$1000,0),countif($m$2:m2,m2)*2),"")

in O2

Code:

=iferror(index($a$2:$i$1000,match(m2,$a$2:$a$1000,0),countif($m$2:m2,m2)*2+1),"")

Regards

Convert Data from Code/Amount Set to Proper Record

RGottesman

New member

Canapone

New member

Canapone

New member

RGottesman

New member

Canapone

New member

RGottesman

New member

Canapone

New member

Attachments