I am comparing excel column A and B. They have their inputs already and some are blanks. How do i go about to automate the comparing of the values in A and B, and do some marking (e.g. if values in B is different from A, maybe flag out?)
@TyraS For understanding of the Complete Scenario, It is best to provide some Sample Input Excel Files on which you want to perform operation. Explain the operation that you want to do in Steps and maybe a Sample Output in Excel for the Input data provided. It will be easier to understand, ask relevant questions if needed, and also faster to solve the problem.
I’ve included the excel files.
Both the input files are placed a in folder each(they are filled in different rows), I gotta read them and put the column B values into the Output file. Output file only has Column A. No values in Column B. After both input files have been placed into the Output file, I have to flag the row which has different values InputFile1.xlsx (8.2 KB) InputFile2.xlsx (8.1 KB) OutputFile.xlsx (8.1 KB) in Column A and B.
@TyraS Expected Output File Should contain the output as to how you need it. I still think the Output File that you have provided is incomplete .
What do you mean by different rows and How to Flag?
Is the Below Screenshot the Output for the Data you have provided ?
the output file should be the combined input files. um, flag as in maybe input a colour on the cell itself?
@TyraS Coloring is possible but is the above Output that I have Shown in Screenshot proper? Is that the output you need ? Also Please provide a definite Output and also how you want the Coloring to be done for cells which don’t have data in Column B.
Hi, yes the above output is correct, without the flag column that is. Coloring should should be on the Column B cell if the value is different from the Column A value… For empty cells in Column B we shall leave it as it is… can i know what do you mean by definite output?
Hi SuperMan. I have the same problem but a slightly different scenario. i have attached the file Sheet Jan is the base sheet sheet feb is the desired output. the objective are Compare to last month sheet. If change in any cell. That cell gets highlighted. Along with any of the primary identifying Key like emp ID or emp name. If row that was not in last month when compared with this month . The whole row get highlighted in green. If row that was in last month when compared with this month and not in this month. That row get copied from last month gets added to the bottom of the table preferable by having a space of 2 or 3 empty rows and is highlighted in red. The colours dosent really matter you can chose any 3 colors. Emp.xlsx (12.3 KB)
@TyraS Check this workflow :
I have used Join to join the Datatables so that all the rows are in line with each other. Then I just shift the values in Other Column B to the Original Column B. Also for this to work the number of rows in Sheet1 of InputFile1 and Sheet1 of InputFile2 should be the same. Test this workflow on all data that you have and then revert back if you found any errors.
JoinDT.zip (14.6 KB)
@shaki You have provided the Excel which is actually the Output. Can you provide the February Sheet with No Colouring So that I can work on it?
Yes it works, but if i insert a column between A and B, the copied data ends up in the Middle Column. Can i know what does these 2 Assign means?
@TyraS Based on the actual data format that you have we might need to modify few things in the query.
The resultDT keeps the Schema Format or the Column Format of the First Input File, Then It loads the data to it as needed with Just Two Columns First being the Column A values and 2nd being the Column B values.
In your case Since the Column has been added in the middle between Column A and Column B, the Data of Column B might be in the Column that you have added.
If you can provide the original Format of the Files with Some sample data in it. I can modify the workflow to get the needed output.
Um. I’ve attached the files. InputFile1 will have data on Columns C & D, and InputFile2 will have data on Columns C& D, OutputFile will be in the folder too, and Column A & B for all the files are same and standardized. The idea is to compile the data from InputFile1 and InputFile2 's Column C&D, and fill into OutputFile’s Column C&D, then do the comparing and coloring. However, have to base on the header names as the columns might be switched.InputFile1.xlsx (8.3 KB) InputFile2.xlsx (8.3 KB) OutputFile.xlsx (8.2 KB)
@TyraS Again I would like to confirm if the Output that you need and the Output that I think you need is right and the same So is the below Screenshot the required output ?
Hi the coloring is wrong. Column C to compare with Column A, and Column D to compare with Column B. sorry think i missed this out.
So if Value in column c is different from A, will put red, and if D is different from B, will put red too
@TyraS Ok. I think I got it to work the way you wanted it. Check the Workflow. Test it for all possible inputs that you have for that kind of data. I have changed the Color to Yellow. But you can change it to the Color of your need.
JoinDT.zip (14.8 KB)
Here you go Feb is now included
data set 1.xlsx (13.8 KB)
Yes yes thank you. But do you have any idea why when i increase the row of data to 28 rows. The output became 82 rows instead?
@TyraS That shouldn’t be the case Unless, there are multiple matches. So the 28 rows of Data are present in both the Input 1 and Input 2 ?
Yes, both input files have 28 rows.
The output came back with 82 rows, and quite a few duplicates i think