Search for excel row using value from datatable

Help
,
1

Hi, I have two excel worksheets as data tables. The first data table called ‘GoodsCodes’ contains codes that will be used to search the Excel worksheet (As seen in the first screenshot in column A)

What I want to do is use the values stored in Goods Codes, search NomenclatureInfo (data table of screenshot 2 worksheet) and then copy columns B-G which contains the goods code in column A. The output of this will be back into the first worksheets Column B ‘Description’.

ie. Find the goods code number in Column A like below.

image

Then I want to copy column B-G as shown below relevant to the correct column A.

image
image1050×203 8.74 KB

These are then the result I would expect to pick up. (Note how I skipped one row as it contained a duplicate value ‘- - -’ in column F. I must do this in all cases if it appears.

I am new to this and any feedback is greatly appreciated. Thanks :slight_smile:

NomenclatureScrape.zip (1.2 MB)

2

Use the Join Datatable activity to join the two datasets using an Inner join. Then use Remove Columns to remove the columns you don’t want.

3

I would need all columns on the desired line.How could I specify that I wanted the rows that contained the right code in column A. For example, the code I entered was found in A20,A21,A22,A23 - How can I then say to keep column B,C,D,G of the same lines and nothing else?

4

If you use an inner join, then anything not in the 2-column workbook will be removed. Therefore, codes in the larger workbook not found in the smaller one won’t be in your datatable.

6

Hi @dwalker

It was in interesting challenge. I think I got it, although it duplicates the last Other if one didn’t provide the full 10 digit code in your input file.

The logic behind the extracting sequence is that it filters for nomenclature table based on an input code 5 times, each time replacing 2 digits from the end with 00.

See the below project, let me know if you have any questions :slight_smile:
NomenclatureScrape.zip (1.2 MB)

1 Like
8

You definitely got it. A lot more complex than I originally imagined it to be.

Thanks very much this has been a lot of help :slight_smile:

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.