nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Adding rows in one column keeping values from another then moving !

Birdman1971

Hi everyone

I have tried a few things now and i seem to be going round in circles .

I have some data that i need assistance with please.

I have data in several columns in excel.

Column B will have for example 20 rows with a code of 9000222 and column C will have a different value per row. So B is cost centre and C is an amount.

I need to create a contra value - so those 20 rows with 9000222 will need to have 6000111 inserted , with the same values held in column C but then moved to Column D .

This occurs several times in the file .

So for example 9000233 will also need 6000223 inserting with the values in C copied but moved to D .

Any help , guidance appreciated.

Workflow

Datasets

Tips and Tricks

Accepted answers

AncientPandaman

yes, idea is create a map file, where the opposite cost center. remember reverse set if it happen in reverse way.

then just join and replace the value and reverse the credit and debit.

union with original data and sort it.

workflow:

If the pair cost center required rules, like if > 50 is 6000110, between 50 to 100 is 6000111, >100 is 6000112. try to add these threshold to the map and do the filtering after the join.

Journal Entry.yxmd

All comments

Dhrish

Hi @Birdman1971

If you have a mapping file that maps each cost center Column B 9000223 to the Column D i.e. 6000223

Cost Center

Column D

9000223

6000223

then you can use this file to join to your existing data.

Cost Center

Amount

Column D

9000223

12132

6000223

9000223

34324

6000223

and then just use a formula tool to concatenate the amount with this Column D.

Hope this helps

AncientPandaman

yes, idea is create a map file, where the opposite cost center. remember reverse set if it happen in reverse way.

then just join and replace the value and reverse the credit and debit.

union with original data and sort it.

workflow:

If the pair cost center required rules, like if > 50 is 6000110, between 50 to 100 is 6000111, >100 is 6000112. try to add these threshold to the map and do the filtering after the join.

Journal Entry.yxmd

Birdman1971

Thanks so much - sorry for the delay , have been away for a few days. Appreciate all your help

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703