Excel Vlookup / IF / Textbefore

Copper Contributor

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!!!

 

 

 

 

 

 

5efcf9d1-7b7b-418d-b636-349d2af7f036.png

10 Replies

@ChrisRolando 

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.

Cross posted here on the same date

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.

@ChrisRolando 

 

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.

@mathetes 
My friend, THANK you.  Nice to know I am not the very oldest goat working in this stuff (still hanging on to my 60's).  
I knew this was going to be a wild one and I just wanted to see if anyone who looked at it went "Oh yeah, just do THIS".

I am going to have to go the route of a picot table to get everything where I can see it.  The biggest challenge is that this is all LIVE data so I cannot just freeze anything and then move it around.  On GO-LIVE day, this will be an export to CSV and an upload to the database CRM and then I am going out for a Martini or two.

Again my friend, thank you for taking a look and I do so appreciate you time,  Happy 2023!

@ChrisRolando 

This may work for you. I see you have access to TEXTBEFORE so you also have XLOOKUP (No need to involve 24+ columns with VLOOKUP).

 

The formula is a bit longer but more flexible:

=LET(data,$M$2:$AM$10000,store,TEXTBEFORE(INDIRECT("J"&ROW()),"-")*1,col,XMATCH("*"&INDIRECT("K"&ROW())&"*",$M$1:$AM$1,2),XLOOKUP(store,TAKE(data,,1),CHOOSECOLS(data,col),""))
Going to give it a try and Thank You!
You have used a number of functions I have never used and I hate to appear just plain stupid, but I have to ask:
The items you show in white in your formula (Store | Data | data,col): These appear to be references that I need to add. Am I correct? And if so, I need a bit of a better understanding as to what I am looking for for each reference.
I am sorry for having to bug you with this, and Thank You in advance!


@ChrisRolando 

 

Chris -- those references in the formula @Patrick2788 gave you are references that are "created" in the course of using the LET function. The LET function is both a lot of fun, AND a very powerful tool in the new arsenal of Excel features. Well worth learning.

 

So it's the LET function that you need to familiarize yourself with. Here's a link.

@ChrisRolando 

I recommend going through the Excel Jet link @mathetes posted.  The site starts with a very basic example, but you'll want to keep digging a bit to get to more sophisticated uses of LET.

Here is another good source:

Announcing LET in Excel (microsoft.com)