Joining Two Inputs & Filtering

Question

Hey,

A couple things I'm trying to do here:

1) Imagine the two tabs in the attached file are separate inputs. I want to connect the 'BCC' column in Sheet1 to the 'CC' column in Sheet2, so that other columns can also populate.

For example, I want to match "998-England" in Sheet 1 to "998" in Sheet2. This will also allow the 'BID' and 'Name' column from Sheet1 to be joined to Sheet2. For this example, that would be BID "12" and Name "Harry".

2) Once that is done for all rows, I'm trying to create an output where only the values between -100,000 and +100,000 are shown. I don't want to show any numbers outside this range.

Hope this makes sense!

Thanks,

Tom

Buildings.xlsx

BS_THE_ANALYST · Accepted Answer

@tomtingley Hi! 
I'd take this route:

If you want to match the two columns from the two sheets, create another column in Sheet 1 that removes the punctuation and letters. I.e. 998 - England -> will then go to 998. This will allow a clean match with the second sheet.

I did this here. Created a dummy column (replicating the column), then used the data cleansing tool to strip away the letters and punctuation.

After this, you can make that join!

For your next requirement, I manipulated the data to get all the values into one column using the transpose tool:

Once they were at this stage, you can use the filter:

This will retain only the values you're interested in.

However, if you want to go a stage further, and pivot it back into the original table, you can use the CrossTab tool. It will pivot it back to how it originally was, but you will notice that the values that don't meet your filter criteria will be replaced with Null():

Hope that helps.
BS

SmitaLohande · Accepted Answer

hi @tomtingley

Please find attached the solution.

You can use 'Text to Columns' tool to split the CC column from 1st sheet. below is the configutarion of the same, which will give you 2 extra columns.

You can then join CC1 to the CC column from the right input of the join (this looks at the second sheet from the workbook).

Then use the formulatool to do below for each of the amount column.( I have done this just for 2 columns as an example)

Please mark it as a solution if it helps ! :)