Locate a value in one excel in another excel

[BobjiK]

Hi, I am new to Alteryx development. I would like to request for help.

Below are 2 excel tables. I need to locate the vendor no that is there in FP Data, in the excel, Triage Data. Once it is found, I need to remove the particular row from the FP Data and write it into another excel. Please let me know how can I prepare the workflow

TRIAGE DATA

Duplicate Group	Division	Vendor No.	Vendor	Internal Ref.	Invoice No.
1	Div 1	20000	Vendor 1	Ref 1	Invoice 1
2	Div2	30000	Vendor 2	Ref 1	Invoice 2
3	Div3	40000	Vendor 3	Ref 1	Invoice 3
4	Div4	50000	Vendor 4	Ref 1	Invoice 3
5	Div 1	55000	Vendor 5	Ref 1	Invoice 3
6	Div2	34000	Vendor 6	Ref 1	Invoice 3
7	Div3	53000	Vendor 7	Ref 1	Invoice 3
8	Div4	12000	Vendor 8	Ref 1	Invoice 3
9	Div 1	11000	Vendor 9	Ref 1	Invoice 3
10	Div2	29000	Vendor 10	Ref 1	Invoice 3
11	Div3	56000	Vendor 11	Ref 1	Invoice 3
12	Div4	77000	Vendor 12	Ref 1	Invoice 3
13	Div 1	45000	Vendor 34	Ref 1	Invoice 3
14	Div2	33000	Vendor 66	Ref 1	Invoice 3
15	Div3	36000	Vendor 76	Ref 1	Invoice 3

FP DATA

Risk	System	Division	Vendor No.	Vendor	Internal Ref.	Invoice No.
High	Sys1	Division 1	20000	Vendor 1	Ref 1	Invoice 1
High	sys2	Divison 2	30000	Vendor 2	Ref 1	Invoice 2
High	Sys1	Division 3	40000	Vendor 3	Ref 1	Invoice 3
High	sys2	Division 4	50000	Vendor 4	Ref 1	Invoice 3
High	Sys1	Division 5	55000	Vendor 5	Ref 1	Invoice 3
High	sys2	Division 5	34000	Vendor 6	Ref 1	Invoice 3
High	Sys1	Division 6	53000	Vendor 6	Ref 1	Invoice 3
High	sys2	Division 7	12000	Vendor 8	Ref 1	Invoice 3
High	Sys1	Division 7	11000	Vendor 9	Ref 1	Invoice 3
High	sys2	Division 8	29000	Vendor 10	Ref 1	Invoice 3
High	Sys1	Division 8	56000	Vendor 11	Ref 1	Invoice 3
High	sys2	Division 9	77000	Vendor 12	Ref 1	Invoice 3
High	sys5	Division 10	45000	Vendor  12	Ref 1	Invoice 3
High	sys8	Division 11	33000	Vendor 14	Ref 1	Invoice 3
High	sys9	Division 12	36000	Vendor 88	Ref 1	Invoice 3

[Prometheus · Accepted solution]

@BobjiK You can join the two datasets on Vendor No. Any records from FP data that don't join will come out of the Join tool. You can take these records and write them to another Excel file, thus "removing" the records where the Vendor No. in FP is in the Triage data file.

 

[caltang · Accepted solution]

I would use thé Join tool to connect them via the Vendor No. as the primary key for both tables. If they appear in J, then you’ve already technically removed them from the FP data since the Join tool works with L, J, and R outputs.

I would assume the Triage Data is the left side and the FP data is the right side. So whatever comes out of the right side is whatever that is not found in the Triage data.

[BobjiK]

Thanks. Let me try this solution

[BobjiK]

Thanks. Let me try

[BobjiK]

Thanks Prometheus and Caltang. The solution works. I would like to format the spreadsheet before it is written in the output folder. I know it needs a combination of tools like Render, Layout. Can you please let me know how this can be achieved.

[caltang]

Let us know how you want it to look like. 

cc @Prometheus 

[BobjiK]

I want the final output to look like format as table in Excel. Font to be Arial Nova and the size set to 9. 

[BobjiK]

There is also one other requirement. From the table FB data, I need to remove the very next line alongwith the line that has been located in the Triage table. Please help.

I thought of Select tool but by having it before the Join tool, it will remove some lines before going into the Join Tool. If I include Select Tool after Join, by the time the records are already separated and Select Tool as such will not have any use.

[caltang · Accepted solution]

There's no Arial Nova. I chose Arial. Have a go.