Allocating rows to new columns when identifier is only in one row

Hi all

I am trying to work out a way to allocate totals to a particular column based on an idenitifier existing in anoher column. I have included the input data in the attached and shown what i would like the output to look like.

If in Column C there is the identifier 804020, then you take its value in Column A (1400022727) and for all of those you put the number from Column D in Clear 3.

If in Column C there is the identifier 259440, then you take its value in Column A (1400023144 and 1400023309) and for all of those you put the number from Column D in Clear 1.

If in Column C there is the identifier 259430, then you take its value in Column A (1400023482) and for all of those you put the number from Column D in Clear 12.

If none of the identifiers apply you take all the values from Column D and put them in "Stays:.

draft.xlsx

Workflow

Accepted answers

Qiu

@N89
Just manage to get similar results as your output. Need to do something about the formatting, though.

1020-N89.yxzp

All comments

Qiu

@N89
Just manage to get similar results as your output. Need to do something about the formatting, though.

1020-N89.yxzp

grazitti_sapna

Hi @N89 , give this a try.

Thanks.

Allocation-GS.yxmd

N89

Thanks @grazitti_sapna - my problem is in my actual data set i have thousands of different Column A numbers so i can't input in the formula the wya you have done. I will try @Qiu solution with a bit of a workflow in the beginning to make my data look like the second input too without manually doing that.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703