Building a Checking and Replacing Section for column in the workflow

[k3pineapple]

Hello All,

need help with a work flow

I am going to add a checking and replacing section in my workflow. In my raw data , the sub-products which is under the main products list under the same column header as the main products , I have already split them.

Now I create a separate sheet about list of main & sub products to add a checking and replacing step in order to convert the sub product name to the main product name.

I have over 10 main products and over 500 sub products under them, the single formula I built is not suitable for this, since asking for help , thank you.

Here is the example: 

the sample products (main & sub) category sheet I created:

Main	Sub
A	a
A	b
A	c
B	x
B	y
B	z
C	ab
C	cd
C	ef
C	gh
C	ij
D	abc
D	bcd
D	efg
E	aaaa
E	bbbb
E	cccc

2. the filtered data:

Company	Product	Sales	ID
A1A	b	Marry	123456
A1A	x	Peter	234567
A1A	y	John	345678
B2B	efg	Amy	456789
B2B	aaaa	OK	567890
C2C	b	Hello	112233
C2C	x	Hi	223344
C2C	ab	Jack	334455
C2C	cd	Rose	445566

3. Expected Result

Company	Product	Sales	ID
A1A	A	Marry	123456
A1A	B	Peter	234567
A1A	B	John	345678
B2B	D	Amy	456789
B2B	E	OK	567890
C2C	A	Hello	112233
C2C	B	Hi	223344
C2C	C	Jack	334455
C2C	C	Rose	445566

Attached file is the data shown above.

Thanks again

[flying008 · Accepted solution]

Hi,@k3pineapple 

you only need use find&replace tool to get.

1- Find Within Field: [Product]

2- Find Value: [Sub]

3- Select : Entire Field , Match Whole Word Only

4- Replace Found Text With Value: [Main]

[Qiu · Accepted solution]

@k3pineapple 
We can use a joint to do this. :)

[k3pineapple]

@flying008 Thank you very much for your answer, 100% match what I want here...

right now, I need to build the next step which is to revert back for these data.

Cells in the product column: Main (has been converted before, exclude the data which is main before) -> sub

Thanks again.

[k3pineapple]

@Qiu Thank you for your help again. Like your answer.

I would like to revert these data back to the original data in the product columns now.

Details:

Main(only the data has been converted to sub before) -> Sub

e.g.

A -> b (this is the cell, that has been converted before, now need to convert it back)

A -> A (not be converted in the flow showing above)

I hope can get your help again. Thank you

[Qiu]

@k3pineapple 
Thank you for the feedback, I will check it later.

[Qiu]

@k3pineapple 
I was not sure about which one is the " original data" you mentioned. just guessing :)

[k3pineapple]

@Qiu  Thanks for the workflow, Sorry, I did not explain it clearly. this is what I want now

Matrix 1 -> 2 we have already solved

Matrix 2 -> 3 is the workflow I would like to build and being stuck there, since I need to build a flow to differentiate what products has been converted from sub to main later of the flow during the step 1 -> 2

For this time, I suppose in the first join," product = sub " does not work in this situation 

And thank you again for your help.

1. Original data

Company	Product	Sales	ID
A1A	b	Marry	123456
A1A	x	Peter	234567
A1A	y	John	345678
A1A	A	Marry	123456
A1A	B	Peter	234567
B2B	efg	Amy	456789
B2B	aaaa	OK	567890
B2B	A	Amy	456789
B2B	B	OK	567890
C2C	b	Hello	112233
C2C	x	Hi	223344
C2C	ab	Jack	334455
C2C	cd	Rose	445566

2.Result Data

Company	Product	Sales	ID
A1A	A	Marry	123456
A1A	B	Peter	234567
A1A	B	John	345678
A1A	A	Marry	123456
A1A	B	Peter	234567
B2B	D	Amy	456789
B2B	E	OK	567890
B2B	A	Amy	456789
B2B	B	OK	567890
C2C	A	Hello	112233
C2C	B	Hi	223344
C2C	C	Jack	334455
C2C	C	Rose	445566

3. Result revert

Company	Product	Sales	ID
A1A	b	Marry	123456
A1A	x	Peter	234567
A1A	y	John	345678
A1A	A	Marry	123456
A1A	B	Peter	234567
B2B	efg	Amy	456789
B2B	aaaa	OK	567890
B2B	A	Amy	456789
B2B	B	OK	567890
C2C	b	Hello	112233
C2C	x	Hi	223344
C2C	ab	Jack	334455
C2C	cd	Rose	445566