Compare Two Sheets, Copy Data

[mightymo77]

Hello everyone. I have one for you. Attached are Alert.csv and tracking.xlsx. What I am trying to do are two things.

1 - Alert.csv columns A (First Name), B (Last Name), C (User Email), D (Device Name) and U (OS Name) need to be compared to the data in Tracking.xlsx, and if the data of Alert columns A and B (or C if it's easier) is NOT present in Tracking, then I need Alert columns A, B, C, D and U copied to Tracking columns A, B, C, D and E on the first available row (currently 21).

2 - In Alert.csv, columns F, G, H, I, J, K, L, M, N, O, S and T will contains values of either PASS, FAIL, WARNING or NA. If the values are either FAIL or WARNING I would like the title of that value listed in column X.
For example - Row 16, Preston Soloff has either a warning or fail on: Password, Anti-Spyware, Disk Encryption. So in X16 I would like that cell to read "Password, Anti-Spyware, Disk Encryption".

Is this possible? I would really appreciate any help!

[DJunqueira]

I think a good solution would be using Power Query, but there is a column header adjustment that need to be decided, it is easy to solve, but it is you who has the last say.
The .csv is imported directly to Power Query and then appended to the named table in Tracking.xlsx, then filter column D for unique value.

Please Login or Register  to view this content.

[mightymo77]

I will need to figure out how to apply that code in Power Query. Never used it before. I also noticed a typo in your code "Traking" just fyi.

[DJunqueira]

I will need to figure out how to apply that code in Power Query. Never used it before. I also noticed a typo in your code "Traking" just fyi.

misspell, that happens...

Power Query is very good and simple tool to work with data, is the same tool of Power BI, so learning it in Excel also gives you knowledge in Power BI.
The code really represent 3 pressed buttons, is a requirement of the forum to show it, you don't white this thing.
Once you start using it you don't stop, lots of videos about it in Youtube, highly recommend!

An introductory video about it:
How Power Query Will Change the Way You Use Excel

[mightymo77]

OK thank you. I don't have that much time (right now) to figure out how to use Power Query.

Back to my two questions in the first post, is there a formula that can resolve question #2? That is the very time-consuming procedure, so if there is a formula that I can use that would be great.

[kvsrinivasamurthy]

Back to my two questions in the first post, is there a formula that can resolve question #2? That is the very time-consuming procedure, so if there is a formula that I can use that would be great.

In X2 of Alert File then copied down. ARRY formula used.

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND($F2:$T2,"FAIL,WARNING")),$F$1:$T$1,""))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[mightymo77]

Hey guys. Using kvsrinivasamurthy's solution above, works great on my home machine (Excel 2021), however on my work machine (Excel 2016) using the same exact .csv file, it is not working and producing a #NAME? error. Any ideas?

EDIT - I looked into it and TEXTJOIN apparently does not work on Excel 2016. I think maybe If(LEN may work? Can anyone change kvsrinivasamurthy's formula to one that functions in Excel 2016? Thank you for your help!

[kvsrinivasamurthy]

If TEXTJOIN Is not working it is better to go for UDF. I will give code for that. The file is to be saved as .xlsm not as .csv.
Code for UDF

Please Login or Register  to view this content.

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[mightymo77]

Thank you all for the excellent assistance.