Hello Community I need to look up part of a description in the "Description" column and when found rename the corresponding data in the Item Number column. Not sure if this would be a Formula (If/then with embed Contains) or one of the Rename tools. In the sample data below, the look up data of 304 or 316 varies in location within the description. Once either is found the corresponding Item Number R320 should be renamed as S304 or S316. Only been working in Alteryx for a month or two. Appreciate your insights. Sample Data Item NumberDescriptionR326WC.072MINX49.600 GR33 G90 UNISR314WC.064X49.5625-1010 HB DOMR320SC.071X3.773 ST 316 SSR314WC.148MIN X 47.00 1015 HBR326WC.099MINX52.7500-SS GR55 G90R300WC.043MINX48.1250-1008 CRR500WC.056X53.1250-1008 HB DOMR320SC.071X3.773 PS500/P4100 304SSR320SC.071X5.450 PS210 304 SSR500WC.0575MINX48.00-1006HB DSR320SC.100X3.790 ST 316SSOBR308WC.083MINX50.000 SS GR55 ZAM90R300WC.052MINX48.0600-1006 CRDSR308WC.105MINX52.750 SS GR55 ZAM90R320SC.100X5.256 PS200/P1000 316SS Results Item NumberDescriptionR326WC.072MINX49.600 GR33 G90 UNISR314WC.064X49.5625-1010 HB DOMS316SC.071X3.773 ST 316 SSR314WC.148MIN X 47.00 1015 HBR326WC.099MINX52.7500-SS GR55 G90R300WC.043MINX48.1250-1008 CRR500WC.056X53.1250-1008 HB DOMS304SC.071X3.773 PS500/P4100 304SSS304SC.071X5.450 PS210 304 SSR500WC.0575MINX48.00-1006HB DSS316SC.100X3.790 ST 316SSOBR308WC.083MINX50.000 SS GR55 ZAM90R300WC.052MINX48.0600-1006 CRDSR308WC.105MINX52.750 SS GR55 ZAM90S316SC.100X5.256 PS200/P1000 316SS Description Example.xlsx

How to look up data in one column and rename a different column

Hello Community

I need to look up part of a description in the "Description" column and when found rename the corresponding data in the Item Number column. Not sure if this would be a Formula (If/then with embed Contains) or one of the Rename tools. In the sample data below, the look up data of 304 or 316 varies in location within the description. Once either is found the corresponding Item Number R320 should be renamed as S304 or S316.

Only been working in Alteryx for a month or two.

Appreciate your insights.

Sample Data
Item Number	Description
R326	WC.072MINX49.600 GR33 G90 UNIS
R314	WC.064X49.5625-1010 HB DOM
R320	SC.071X3.773 ST 316 SS
R314	WC.148MIN X 47.00 1015 HB
R326	WC.099MINX52.7500-SS GR55 G90
R300	WC.043MINX48.1250-1008 CR
R500	WC.056X53.1250-1008 HB DOM
R320	SC.071X3.773 PS500/P4100 304SS
R320	SC.071X5.450 PS210 304 SS
R500	WC.0575MINX48.00-1006HB DS
R320	SC.100X3.790 ST 316SSOB
R308	WC.083MINX50.000 SS GR55 ZAM90
R300	WC.052MINX48.0600-1006 CRDS
R308	WC.105MINX52.750 SS GR55 ZAM90
R320	SC.100X5.256 PS200/P1000 316SS

Results
Item Number	Description
R326	WC.072MINX49.600 GR33 G90 UNIS
R314	WC.064X49.5625-1010 HB DOM
S316	SC.071X3.773 ST 316 SS
R314	WC.148MIN X 47.00 1015 HB
R326	WC.099MINX52.7500-SS GR55 G90
R300	WC.043MINX48.1250-1008 CR
R500	WC.056X53.1250-1008 HB DOM
S304	SC.071X3.773 PS500/P4100 304SS
S304	SC.071X5.450 PS210 304 SS
R500	WC.0575MINX48.00-1006HB DS
S316	SC.100X3.790 ST 316SSOB
R308	WC.083MINX50.000 SS GR55 ZAM90
R300	WC.052MINX48.0600-1006 CRDS
R308	WC.105MINX52.750 SS GR55 ZAM90
S316	SC.100X5.256 PS200/P1000 316SS

Description Example.xlsx

Designer Cloud

Accepted answers

jdminton

@JTWilliams There are a few ways to do this, but is there anything that can be a bit more specific on the search? I could see the possibility of having 304 or 316 in the description elsewhere. Is it always in the same position within the string, for example?

I've included two methods that can do this based on your sample, but with larger sets of data, you may run into issues with those numbers appearing where it isn't relevant.

Update column.yxmd

All comments

jdminton

I've included two methods that can do this based on your sample, but with larger sets of data, you may run into issues with those numbers appearing where it isn't relevant.

Update column.yxmd

JTWilliams

jdminton

Thank you for the two different approaches. I am fairly new to Alteryx and not yet familiar with Regex. Not sure I fully understand the formula or how it works. The second approach "number appear anywhere..." worked. Variations of 304 & 316 did show up in the larger data set and needed to adjust the formula to filter out the unwanted data.

Question - in the "number appear anywhere..." approach, I don't see how the Record ID and Select Tools are being used. Am I missing a nuance in the flow?

Thank you

jdminton

RecordID I use a lot in the event I need to sort back to the original order. In this case, it isn't needed unless you will need to resort at some later point in the workflow. Alteryx does not always maintain your sort order. You can ignore the select tool after the formula. The other was to remove some of the columns parsed.

The RegEx in the top formula is just removing double spaces (2 or more). If you try to use text to columns and space as the delimiter, it will make a column for each time it encounters a space. By changing where there are two or more spaces, it aligns the columns. On second look, some of the descriptions have no spaces, which means they won't align correctly if you tried to do something different that involved those records. The highlighted below are an example of this. They don't meet the criteria of your original request though so I did not worry about those.

JTWilliams

Thanks again for the solution.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703