Populate excel with selected columns in another excel with search word (partial matching)

Help
1

Hi,

I need to populate file1 with values in file2 and file3 based on a key word. The key word is columnA in file1. The search word is part of the value and not exact match, eg ‘13904’ could be found in ‘LIB-NB13904-2’ and then return the targeted columns and write into file1.

The logic is
(1) Read columnA in file1, say ‘13904’
(2) Use ‘13904’ to search in file2 for the corresponding value in columnA, in this case ‘LIB-NB13904-2’
(3) Write the value in file1, columnB
(4) Again, use ‘13904’ to search in file2 for the corresponding value in columnD if columnC in file2 has ‘wireless’, in this case, columnD returns ‘90:61:AE:C0:86:D1’
(5) Write the value found, ie ‘90:61:AE:C0:86:D1’ in file1, columnC
(6) Using still the same value ‘13904’, now search file3 columnB for a row that has ‘13904’ as part of its value. Then return columnA, in this case ‘90-61-AE-C0-86-D1’ and write into column E of file1.

I have attached the files for reference, Thanks

file1.xlsx (9.6 KB)
file2.xlsx (776.2 KB)
file3.xlsx (82.5 KB)

2

@abcdefg

What was your question here? can you share what you have done so far?

Regards

3

Hi @albert.yango

Thanks for checking out. I am not able to find any info or features that would help in building my project. As such, require a sample based on the logic and files provided.

Basically, it’s about populating selected columns in file2 into file1 using a key word in file1 to search. The key word need not be a exacr matching word as it is part of a word in the target. Please provide a sample if possible so that I could go thru on the technicques or features used. Much thanks!

4

I created the step 1 - 3. You can do similar logic on the rest of your process.

Sequence2.xaml (13.9 KB)

Goodluck :smiley:

5

Thanks @albert.yango for the quick response.

I tried running. The programs went thru many loops.

Please refer to the picture, The value returned for row 5 is updated into the header column and not the corresponding row5.columnB.

Capture1 Also, rows 2-4 are not populated. Row2 does not have a match though and appreciate if u could help with the codes for an ‘NA’ too. Looking forward to your help. Now i know it’s the contain formula.

7

There you go! :beers:
Just add log fields and you are good to use.

ExcelFilesLookup_abcdefg.xaml (26.5 KB)

Also some filters in File3 return more than one rows, where this logic defaults to getting content from the first row. That is something you might want to check once.

8

@priyankavivek
Do you have any open thread with this issue? to get the details of your question?

9

Thanks @kaderms for the prompt response. I will try to figure out your codes. Looks pretty advanced.

But first, could you just help a newbie like me. I am not able to run the program after placing into my working directory. Is it because of some hardcoding in the Rread Range ? Are you using my files for testing as I see the codes have different sheet name.Capture2 I tried to change the codes using my working folder, still face the same error as attached. Thanks!

10

@abcdefg I am using the same file name as yours. But I kept it in a folder named Data.
You can also place it like that - in your folder with Main.xaml, create a folder named “Data” and place all three files in there. Once execution is completed, you’ll also see an output folder in the same place with the updated file.

Or You can also change the file path - double click on the first sequence - Read all data and you’ll see three different excel scopes. You can change the file name in these activity blocks.

File names are hard coded. You can change them if you want. But sheet names are parameterized. Check the variables pane.

Feel free to ask anything that you don’t understand.

1 Like
11

Thanks @kaderms for being so helpful. I need some touch-up to the codes. It’s simple by your standard I suppose.

(i) I would like to change the value returned from ‘A0:C5:89:A0:4A:F6’ to ‘A0-C5-89-A0-4A-F6’. That is, format in ‘-‘ instead of ‘:’;
(ii) Then compare columnC with columnF and indicate in columnG ‘Yes’/’No’
(iii) Also, since one ID may have multiple hostname, then the program should write into a file of every record instead of appending returned columns in existing file1. In other words, file1 is used as a search file, then filter file2 and write a new file of records returned.

Because of the multiple hostname, then point(6) will be changed to use hostname to filter file3 as updated below.
(6) Using columnB (LIB-NB13904-2) unique value instead of 13904(columnA) in file1, now search file3 columnB for a row that has ‘LIB-NB13904-2’ as part of its value. Then return columnA, in this case ‘90-61-AE-C0-86-D1’ and write into column F of file1.

Capture3
Capture31694×233 15.9 KB

12

@abcdefg There you go! :beers:
ExcelFilesLookup_abcdefg.xaml (37.4 KB)

1 Like
13

Thanks @kaderms! This is good !

14

This is wonderful @kaderms. Problem resolved.

Also, may I ask you if it’s necessary to use parameter for worksheet in Read Range since it could also be set in input as say “sheet1” or whatever is the sheetname in the excel. Thanks !

15

[quote=“abcdefg, post:14, topic:107822”]
Problem resolved.
[/quote] awesome!

If it is always going to be the same sheet name, say “Sheet1”, then you can mention it directly in the read range activity. :+1:t2:

1 Like
16

Thanks @kaderms. Got it!

17

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