MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Any Excel experts able to help with my huge excel spreadsheet please? Its populated from some absolutely huge national reports.
I have several VLOOKUPs to collect data from other the sheets, this works well.
=VLOOKUP(A55,'Data 3'!$D$2:$O$1000,12,)
At the moment I update the report data weekly, and have to use Find & Replace to abbreviate lots of the information in the reports so that my facing page is legible and concise. I basically want to automate the find and replace so as soon as the report is pasted in, the relevant data is abbreviated.
I’ve been trying to learn how to use the =SUBSTITUTE function to automatically do these abbreviations and have failed miserably…I think it might be designed to populate a new column with the abbreviations rather than change the source data.
I am now wondering if it would be easier to nest a function into the VLOOKUP, so it finds the correct information and abbreviates it when populating my working/facing sheet.
Any ideas please as I’m sure there is a way (any way will do!)
You could record a macro with your find & replace functions in it. Stick new data in, run macro, job done.
Alternatively you could have your data in one page and then another page that converts it via formulas.
Can I use a macro...I'm not sure as often there are three users working and editing the spreadsheet using the web based version of Excel? I will look into that option, thanks
as madhouse says, record it on a macro,
Selection.Replace What:="Old0809", Replacement:="New0909"
otherwise, setup another reference table and vlookup to it
You have three options:
1. create/record a macro to automate what you manually do, as said.
2. create a second column with all the abbreviations, and VLOOKUP that new column. I think you've tried this.
3. create a more sophisticated VLOOKUP that can search for more than just the abbreviated versions. In other words, it can handle the raw, un-abbreviated stuff too.
You could also consider using MATCH and INDEX instead of VLOOKUP, as it gives you more flexibility in what you are looking for, and extracting.
If they’re the same abbreviations can you not just use an IF function? Just list all the abbreviations in the function and their replacements and use that in the cells?
Option 4.... You need a database
What's wrong with some nested if statements?
Better idea could be ask IT dept to provide a data entry solution to a database that you can use as a source for producing reports.
Edit, someone beat me to it.
I have to do this kind of stuff daily & I find the fastest way is to keep a second spreadsheet with a cascading formula builder that concantentes any new phrase, (or in you case abbrivation) I add to the list.in columns C & D
So three columns FUNCTION, C16 = LATEST-ORIGINAL, D217=LATEST-REVISION
Then in Cell E217 copy down =CONCATENATE(E216,",",C217,",",D217) which copies the formula line above (E216) then adds your new phrase.
You can build massive formulas in seconds. For example this strips out numbers
=(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B9,9,"|"),8,"|"),7,"|"),6,"|"),5,"|"),4,"|"),3,""),2,"|"),1,"|"),0,"|"),CHAR(42),"|"),CHAR(43),"|"),CHAR(44),"0"),CHAR(45),""),CHAR(46),""),CHAR(47),""),CHAR(58),""),CHAR(59),""),CHAR(60),""),CHAR(61),""),CHAR(62),""),CHAR(63),""),CHAR(64),""),CHAR(91),""),CHAR(92),""),CHAR(93),""),CHAR(94),""),CHAR(95),""),CHAR(96),""),CHAR(123),""),CHAR(124),""),CHAR(125),""),CHAR(126),"")))
Lock the last one down /ec / esv ENTER ENTER & copy across into a new column of your sheet & Jobs a good un
All read and thanks for the ideas, now I just need to sit down and crunch through it 🙂 Although I've learned VLOOKUP I've never used other basic stuff like IF so need to do some reading up 🙂
Option 4…. You need a database
I concur, is OP an accountant?
Option 4…. You need a database
Yep, I would just use Access. You also haven't got a huge spreadsheet if it is still working in Excel. Once you do you will find Excel is pretty much unusable.
Mike is right, OP needs a DB 🙂
Macros don't work work in online version AFAIK.
Multiple users at the same time = risky.
@samunkim Regex may, or may not, be your friend 🙂