• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Parsing differences in two data sets

johnny4strings

New Member
Hello everyone,

I am really stumped here!!!!

I am working in Excel 2016/365 and am wanting to compare two imports of data and to parse the differences.

Ideally, I would like to bulk copy from the source, then paste data into one tab have a second tab to show me the results of the parsing, or the differences between the two pastes of data in terms of dollar amounts i.e. amounts that had changed. There are headers in the data and the source is a .csv. I would like to rely on a formula as I am not going to be here forever and not many people know VBA.

Please see the example attached for the information with the differences-I have bolded and high-lit the differences.

Would I use a vlookup or a match of some kind to get the ideal result? I am focusing on the "amount " in column J as well as the "docket balance" in column K.
Any help would be greatly appreciated, I thank you for your time and of course, effort!
 

Attachments

vletm

Excel Ninja
johnny4strings
Is there always three rows of data?
Do You really would like to copy&paste something?
Could You send samples of morning and afternoon files ... which should compare?
 

johnny4strings

New Member
Hi and thanks for responding. No, there is not a set amount of data in each pull, sometimes it can be 100 rows, other times it can be 500 rows, it all depends. The sample of the data is exactly like it was in the example I attached to this post initially.
 

vletm

Excel Ninja
johnny4strings
I asked three questions ...
How about those 2nd and 3rd questions?
... or do You means that, You want to continue copy and paste as well as samples are ...?
 

johnny4strings

New Member
1. the number of rows varies
2. yes? the question isnt clear. I am copying a .csv file from notepad and then and pasting the .csv file into Excel.
3.The samples of data are in the sheet i already included. The AM data sample begins in row 5 and ends in row 7 and the PM data sample begins in row 11 and ends in row 13.
thanks for helping me....
 

vletm

Excel Ninja
Okay -
One sunny morning, there are 100 rows and in the afternoon there are 500 rows.
You will copy those 100 AM rows via ... hmm? ... Notepad ... to Excel and
after that You'll also copy 500 PM rows those to get ... hmm?
AM from rows 5 to 7 and PM from 11 to 13 - yes!

The 2nd question was: Do You really would like to copy&paste something? An answer would be Yes or No...
The 3rd question was: Could You send samples of morning and afternoon files ... which should compare? I tried to ask more realistic files ...

I'm understand Your original texts second sentence ...
Why do You use Notepad and Excel?
This seems to be a challenge.
 

johnny4strings

New Member
Why do You use Notepad and Excel?
Hi and thanks for the question. The data comes from a .csv format and I have to paste it into excel for it to run the calculations. Unfortunately, the only way to get the data is from a download, which is a .csv file.
I appreciate you coming back to me.
 

vletm

Excel Ninja
Please,
even try to search Yourself,
how to get data from .cvs-files to Excel.
Seems that something is missing ...
 

johnny4strings

New Member
Hi:

If you are using excel 2016 I will do it using power query as attached.

Thanks
Hi and thanks for responding. I like the idea of power query however there is information I do not require in the result, the 3rd row of data, currently populating row 23. If I can filter out rows that dont have any changes...then this could be a viable option perhaps. What do you think?
 

johnny4strings

New Member
Please,
even try to search Yourself,
how to get data from .cvs-files to Excel.
Seems that something is missing ...
Well I know how to copy a .csv and then past into excel with delimiting, text to data and all. Copying and pasting isnt the issue. Filtering the data I dont need as well as calculating the changes from the morning to afternoon is my goal.
 

vletm

Excel Ninja
johnny4strings
Yes - copying and paste isn't Your issue - but You don't need to use Notepad for that! ... or if You have some extra time .. then ... okay.
As You wrote, there are normally 100-500rows ... and You offer 'three rows' ... it's huge difference!
 

johnny4strings

New Member
Well yes, I offered three rows. The data I have is sensitive and I cannot clean 500 rows-each with 10 elements to edit. The structure of the data is the same no matter if its 3 or if its 500 rows. A formulaic solution, if one is achievable, can be copied I would think. I appreciate the dialog, though Im now wondering if what I am attempting to do is feasible.
 

vletm

Excel Ninja
johnny4strings
My #10 reply has two lines ... the 2nd one tries to ask something ... which would help You to make those sample files!
Those .cvs sample files would have three 'columns' data ... including those J- & K-'columns' .. other 'columns' could be empty (or 'x').
.. or if there is no unique data, then those other that J- & K-columns would have something to find out same row from both datas.
And
How do know which row is from AM-file and which from PM-file ... ooooh!
... You do manually copy those 100-500 rows, do some filtering and so ...
 

Nebu

Excel Ninja
Hi and thanks for responding. I like the idea of power query however there is information I do not require in the result, the 3rd row of data, currently populating row 23. If I can filter out rows that dont have any changes...then this could be a viable option perhaps. What do you think?
Hi:

Yes, if you filter out the 3rd row you will not be having any issues. I would recommend power query for collating and transformation of data above formula or VBA solution.

Thanks
 
Top