• 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

J

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

  • Example.xlsx
    10.5 KB · Views: 9
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?
 
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.
 
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 ...?
 
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....
 
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.
 
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.
 
Please,
even try to search Yourself,
how to get data from .cvs-files to Excel.
Seems that something is missing ...
 
Hi:

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

Thanks
 

Attachments

  • Example (1).xlsx
    400.9 KB · Views: 7
Nebu said:
Hi:

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

Thanks
Click to expand...
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?
 
vletm said:
Please,
even try to search Yourself,
how to get data from .cvs-files to Excel.
Seems that something is missing ...
Click to expand...
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.
 
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!
 
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.
 
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 ...
 
johnny4strings said:
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?
Click to expand...
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
 
You must log in or register to reply here.
Back
Top