Thread replies: 13
Thread images: 1
Thread images: 1
I'm looking for /g/'s finest MS Excel/VBA magaicians who could potentially crack a painfully annoying issue I have been facing on a daily basis. To put it briefly; I work with sets of data on MS Excel on a daily basis and I frequently have to use the CTRL+F solution to find the criteria I am looking for so I can subsequently replace it with another value. Take the following example:
I have two Spreadsheetss (SS) open- A and B.
SS A- This is actually not just one SS but closer to 70, but just think of it as a single SS with regards to this process. SS A contains 5 columns of data, but we only care about the detail in column E. The data in column is presented in the following manner:
XXXXX;YYYYY;DDDD;Unknown;Nameweneed;ZZZ
In SS A I will have to CTRL+F the word 'Unknown'. This will give a result which will contain the word unknown (not all records in this file contain the word unknown like my example above). I then need to copy the name directly after the word unknown i.e. ‘Nameweneed’ in my above example. This is when SS B is open.
SS B is what would be called the ‘master’ SS as it contains all names and ID’s that are required for this process. The spreadhsset itself has two columns- column A which is the name of the security (‘Nameweneed’ from spread A) and column B which is the security ID we need.
I would control F when SS B is open and paste the ‘Nameweneed’ I have copied from earlier. This will output a row with the exact name of the security I have copied down. We are not interested in the column A result, however, we want the code from column B. This code is copied and SS A is once again opened. Where the word unknown used to be for this particular security we paste over the word unknown with the security ID we have copied from column B of SS B.
This marks the end of the process for one security, but it is continued on repeat until 70 SSs of data (70 more SS A’s) are processed.
>>
It should also be noted that sometimes the name copied down from SS A may not match any of the names in column A of SS B. These particular examples will need to be caught, but require a process too long to automate to fix.
I assume you struggled to read through that cripplingly long-winded description of my task, but that is an issue which I have to laboriously tackle every day.
So my big question is; is there any way that this could be conceivably simplified using a MS excel macro? Any attempts I’ve made at the process myself have been entirely flimsy as I have absolutely no idea where to even start to write the code for this macro. Please /g/, help this MS Excel goblin ease the pain in his fingers. Any advice would be truly appreciated.
>>
Yeah you definitely need to install gentoo first
>>
>>62484623
>help this MS Excel goblin ease the pain in his fingers
How much are you paying?
>>
>>62484685
Will do
>>62484758
I'm more looking for a steer as to the types of commands I would need for this process.
>>
>>62485003
>My time is worthless
ftfy, have fun with your spreadsheets
>>
>>62485003
If I were doing this I would export your spreadsheets to csv and then do whatever fancy manipulations you need to do in python
>>
>>62485121
Thanks friend.
>>62485220
That is unfortunately not an option for me
>>
>>62485349
If this is a recurring problem for you then you're going to have to make learning a new tool an option
If you put your mind to it you can learn python in 2 weeks on code academy
And then just remember this trick I use to parse csv files with python: use the eval() function on each line in the csv file to get an array of the cells, this will work regardless of the formatting of the cells and whether or not there are commas or " marks within the cells
>>
I can automate this with python using openpyxl, but not for free
>>
>>62484606
> https://maxbarry.com/2011/03/news.html
>>
>>62484606
>I work with MS Excel
Found your problem right there, bud.
You ever heard of R, or it's chad cousin, Python?
>>
Sure seems like this would work fine with a macro. Split columns on ; then have another column with an IF() function that spits the name you need into another new sheet... then you just need to run an export macro, combine, and go from there. No fancy programming required.
Thread posts: 13
Thread images: 1
Thread images: 1