Transpose a value that is not zero to a new column in another row

[Goodangel]

Hi all,


I will try to explain my problem in the best way possible. So I am sorry in advance if I am not so clear.

I have a dataset in excel 2013 which shows accounting entries but in rows. An example below. It is not always 2 rows it can be more..

In one row I have the full account, all info filled in all columns (45900.10003919.100234) and in the other row I have the "contra account" (14712.000000.000000) where you can see the default zeros.

Acct$SV$Account Acct$SV$Projects Acct$SV$Award Doc_sequence_value
14712 00000000 000000 287163
45900 10003919 100234 287163


What I want is the following so as to have the information in the same row (Doc_sequence_value is the same for the entries in the ERP software)

Acct$SV$Account Acct$SV$Projects Acct$SV$Award Doc_sequence_value NEW COLUMN
14712 00000000 000000 287163 100234
45900 10003919 100234 287163 100234

All in all I want the Acct$SV$Award that is not zero in a new column like the above.

I tried with index, match, vlookup based on the Doc_sequence_value but I couldn't figure it out. Maybe it is something easy and I can't think of it but I would really need your help!


Thanks so much in advance!

Evan

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Goodangel]

Hi,

Thanks for the quick reply!

Sorry for this, I just uploaded an example.. The blue column is what I actually want

Thanks,

Evan

[bebo021999]

May be try in J2:

=SUMPRODUCT(($H$2:$H$7=H2)*$C$2:$C$7)

[Goodangel]

Hi bebo021999,

So it worked fine as long as I have two rows where one is 000000. If there is something else apart from 000000 then it is a different number but in any case it is not creating a problem.

So it worked more or less and saved me a lot of time!!

Thanks so much!

Evan

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[bebo021999]

Assum:
All duplicate "DOC_SEQUENCE_VALUE" (Column H) get Acct$SV$Award (Column C) from the last DOC_SEQUENCE_VALUE in column H

J2 the drag down:

=IF(H2=H3,J3,C2)