Thread replies: 16
Thread images: 1
Thread images: 1
Hi all,
I'm looking for /sci/'s finest MS Excel savants 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 spreadsheets open- A and B. In spreadsheet A I will have to CTRL+F the word 'unknown'. This will give a result which will contain the word unknown (of course) among other information which I require i.e. 'ABCCorpeation;Unknown;ID1234'. I then manually CTRL+C the ID value at the end of this string of information and open up spreadsheet B. I again CTRL+F and instead of searching for 'uknown' in spreadsheet B, I paste my copied ID. Spreadsheet B is a very simple spreadsheet which just contains IDs and names, so when I paste this ID it gives me a single result and in the column beside it, I can copy down the name associated with the ID. I then go back to spreadsheet A and replace the word 'unknown' with this copied name. Then I would move on.
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. To add to my woes; the spreadsheet 'A' I describe is actually dozens of spreadsheets which I look for the word unknown in and replace with an actual name after matching it with its corresponding ID.
So my big question is; is there any way that this could be conceivably simplified using a several nested functions, because to date I have been struggling to do this or even fathom how to tackle it or what to use. Please /sci/, help this MS Excel goblin ease the pain in his fingers. Any advice would be truly appreciated
>>
>>9164304
You could use VBA, or C# (adding reference to the office assemblies).
/g/ could help, but they'll give you shit at first because LOL, not FLOSS
>>
Several possible ways, ordered by increasing complexity and timeconsumption (2 minutes to 1-2 days of work)
A) Check out the vlookup-Function and its documentation
B) Design a pivot table to do what you wish
C) Write a custom macro
D) Write a C# program that parses your Excel-File and returns a new excel-File with the desired result.
>>
>>9164321
Thanks man, I might holler at /g/ alright. VBA was the method I was thinking for this one
>>9164323
Cheers pal. I'm aware of how VLOOKUP works, but i'm struggling as to how I would implement it when the data is not only separated by semi colons, but that the task I'm performing takes place over so many spreadsheets. Thoughts on how vlookup may be written to attack this issue?
>>
s1 <- read.delim('table1.txt')
s2 <- read.delim('table2.txt')
for (eachLine in 1:dim(s1)[1]) {
currentLine <- s1$info[eachLine]
results <- grep(currentLine,"unknown")
if (results == 1) {
semisplit <- strsplit(currentLine,";")[[1]]
#IDpos <- grep("ID",semisplit)
ID <- semisplit[3] # unless you need to grep in which case use IDpos
nameFill <- s2$personname[s2$ID == ID]
semisplit[2] <- nameFill
currentLine <- paste(semiSplit,collapse=";")
s1[eachLine] <- currentLine
}
}
write.table(s1,"s1 with edits.txt")
listen i just tossed that off in like five minutes so there's probably bugs but you should get the idea. load that up in R and it'll do what you need. and obviously you'd need to edit the filenames and column names
>>
>>9164346
Do you struggle with the indexing? You can reference separate spreadsheets with a hashtag if I remeber correctly. Grab the data from each sheet to one sheet, then have a vlookup-search again maybe?
>>
>>9164359
https://pastebin.com/JbZ0wEqQ
4chan fucked up my indenting
>>
>>
>>9164401
Oh. You would start R, set the working directory to whatever folder contained the sheets you want to load, and then copy those commands into R's command window.
It's a more complicated but more powerful approach to your problem.
>>
>>9164433
And like I said that wasn't really meant to be production worthy, but if you're interested in pursuing that route I can help you flesh it out into a more complete solution. I'd just need to know some more specifics about the task and the files and their structure.
>>
>>
>>9164304
>asking for excel help
>writing 4 fucking paragraphs
holy shit i don't even mind HW threads but you need to fuck off
>>
It sounds like you have a bunch of worksheets with data like "ABCCorpeation;Unknown;ID1234" and then you have another worksheet with just ID and a name. And then you want to put the name associated with the ID into the data in all of the other worksheets?
Am I understanding your problem right?
Easiest way to solve this would be to separate out the ID and then do a VLOOKUP. you could use the function =RIGHT(A1, 6) and it would return just "ID1234" (the rightmost 6 characters) if your data was in cell A1. Then do a VLOOKUP on that against your table of IDs and names and you can put the name right next to the data.
There's also a LEFT() and MID() function for separating out data in a cell. And, if your data is of varying lengths you can do something like =LEFT(A1,LEN(A1)-6)
If you want to get real nifty you don't even have to separate it into 2 steps. You could nest the RIGHT() function in the VLOOKUP() and pop all of the names you want right next to your other data.
>>
>>9164935
I see you also want to actually replace the word "Unknown" with the real name. Check out the SUBSTITUTE() function to accomplish that after you get the names pulled next to the data.
>>
>>9164304
This is more of a /g/ thread.
>>
>>9164448
oh, sorry. I have no idea how to use VBA unfortunately
Thread posts: 16
Thread images: 1
Thread images: 1