Forum Discussion
Excel Vlookup / IF / Textbefore
Hi all;
I have been going along well on a sheet, but I have hit a roadblock and I am hoping it is obvious to someone here.
What I have in the first line of each set, a line with everything I need. However to bring this into another software I need to take the parts in the outlying columns, make them into line numbers using the Work Order reference and the part number to draw those numbers down into the columns.
Looks easy so far right? Here is where it goes off the rails:
Note the line numbers to the left. Note how they jump. This is caused by how this sheet is assembled from 12 tabs with inbound information.
So going to column 5, the formula I have in there is:
=VLOOKUP(VALUE(TEXTBEFORE(J16,"-")),$M$4:$AM$10000,24,FALSE)
What I need to do is carry this and the formulas under it down to the next account block in column L. But cutting and pasting will now work in this case.
I tried using a multiple IF statement on Column L so as to make it a column formula. My thought was to add an IF statement for each of the 5 parts to see if it is in a column within the line... well that is where the wheels came off the school bus and I skidded down the street into the local Dairy Queen... figuratively speaking.
ALL though are appreciated!!!
10 Replies
What I have in the first line of each set, a line with everything I need. However to bring this into another software I need to take the parts in the outlying columns, make them into line numbers using the Work Order reference and the part number to draw those numbers down into the columns.
Looks easy so far right?
No. In fact, what you've written doesn't really make sense. Your first sentence is not even a complete sentence (as your English teacher might tell you), although presumably it means the first line contains what you need...but need "for what" --that's not clear. Nor does what follows--taking the parts in the outlying columns (what are they? can you be more precise?), make them into line numbers using .... etc. If you are going to use Excel to accomplish anything, or ask others to help you, you really need to slow down and describe things with a lot more clarity and precision.
Note the line numbers to the left. Note how they jump. This is caused by how this sheet is assembled from 12 tabs with inbound information.
The cause may be related to the inbound data from those 12 tabs, but there's nothing inherent in bringing data in from 12 tabs that requires line numbers to jump around randomly.
My main thought here, after looking at that image and seeing your description of the VLOOKUP formula you've tried, considering your thoughts on the use of a conditional (IF) function, etc., is that you (and your organization) would benefit greatly from stepping back and doing some thoughtful redesign before trying to make this work. It is just not designed in a way to support long term reliability. If anything, the difficulties you are experiencing is an example of what, even if you manage to get it to work this time, will remain--at best!--a spreadsheet that is next to impossible to reliably maintain, and is as a result liable to produce results that are themselves not to be relied on. You don't want that.
Would you be able to post a copy of the actual workbook--so long as it doesn't contain confidential or proprietary information--so that some of the Excel experts on this forum could take a look at it? You can post a copy (or a mockup devoid of confidential info) on OneDrive or GoogleDrive, pasting a link here that grants edit access to it.
- I am going to attempt this again. I tried to make a lighthearted post, as I am used to in many other tech forums I work in. I may not be 100% up to date on how to use Excel in its current format. I started with Visicalc and an Apple IIe some time ago and have gone along with the changes.
This sheet is a one-time use sheet. I am converting data that one of our sub-companies held in 19 Smartsheets. This sheet is designed to help create the upload sheet that will concatenate all of this data into a CSV file that will be mapped to a current live database program,.
I cannot share this sheet because of the confidential nature of the data. In addition, I cannot go through and change the data in a way where this will still work and many of this sheet's functions are turning text into numbers.
I will try to explain what I am doing and try not to waste anyone's time.
Each row on this sheet where there is a filled cell in Column I is a complete record for this part of the sheet. Column I represents a "program", which is a service contract.
Column J shows a unique number assigned to a Program, where the first part of the number is a Store Number (Store being a business location). The second part of the number is a numerical representation of a date. This combination assures that each number in Column J is unique.
Lines 8,16,25,26 and 28 are sub-lines of line 4. These lines are designed to carry forward inventory items to the main record in the program upload. In column K, you can see the 5 possible inventory items, from each record. The quantity number for each of these rows come from the associated column in the initial Row record, those being columns AH, AI, AJ, AK, AL, AM. I have noted in the headers for each of those columns their column number in the array M4:AM10000.
In the summary sheet that I have shown in this post, there are 2800 Lines that have a reference in Column I (making them a parent line). There are 5 sub-lines (child lines) under each of those lines where I am attempting to fill in the data from the parent lines.
The challenge comes when I attempt to copy the formula(s) that sit in cells L8, L16, L25, L26, L28 and paste them (or in some other way copy them) into L53, L55, L63, L67 and L77. The reason that this continues to fail is that the row numbers under the Parent row vary in their displacement to the row above, This is caused by other calculations going on in hidden columns and on feeder and helper sheets.
For anyone who would like to offer a suggestion, thank you. It is very appreciated.OK, Chris. I too began in the olden days. My first "spreadsheet" was created in 1970 or 1971 by me, using APL, quite literally "A Programming Language,"--a software package developed for and primarily used by mathematicians and scientists. It had the capability of creating multidimensional arrays of numbers, with each cell being addressable. I never used VisiCalc per se, but did make use of a lot of the early spreadsheets, graduated ultimately to Lotus at work, Quattro at home, and ultimately Excel in both locales. I've been retired for a bit over 20 years now, am in my early 80s, have fun helping people resolve issues with Excel. I'm by no means the most accomplished user of Excel--do not use VBA, preferring to figure out how to get from A to B using some of the amazing functions that Excel makes available. I do consider myself to be well informed in databases, database design, the use of tables, and--in general--good reliable design of a spreadsheet.
That was the basis on which I offered the suggestion that you concentrate on redesigning your worksheet because if it were to be used more than once, it's going to be a long term headache for its users.
Now that you've said it's a one-time use sheet, and that your challenge is
to copy the formula(s) that sit in cells L8, L16, L25, L26, L28 and paste them (or in some other way copy them) into L53, L55, L63, L67 and L77. The reason that this continues to fail is that the row numbers under the Parent row vary in their displacement to the row above,
I can see why total re-design isn't appropriate. Given the hints in your paragraph above, I wonder if INDIRECT, with row numbers calculated for those varying displacements--based on yet another set of "helper columns" that show the displacement appropriate for the row-- incorporated in the resulting formulas.
Frankly, though, this sentence
This is caused by other calculations going on in hidden columns and on feeder and helper sheets.
worries me. With all those other calculations going on, in all those places, well, that's why, without actually seeing what you're working with--and I get it, that it can't be shared--but it's very very difficult to offer any substantive and specific suggestions. INDIRECT can be used creatively to modify formulas and references made within formulas IF you know what those modifications need to be. I gather you need to do this not merely on row L, though, but on many more of the 2800 lines in the spreadsheet, and unless there's a fixed and known pattern to those varying displacements, you are working with a monster that is (not telling you anything you don't know) too wild to tame.
Cross posted https://answers.microsoft.com/en-us/msoffice/forum/all/excel-2022-if-vlookup-textbefore/4ba7e58e-2e9c-4cf4-b755-a2209b918966 on the same date
