Excel help

[Crapinon 2311]

I need some help with Excel functions. I have to update pricing on our website by pulling list price from vendor supplied price list. I can get the website content list sent to me in Excel. I would like to set the website content Excel sheet to find part numbers on Excel sheets supplied by our vendors, then import the list price for each item from that sheet to our website sheet. It seems like it should be easy to do but I've searched all day trying to figure out how to do it. I'm sure I'm not putting the correct terms in the search engine. Any help or direction to help would be appreciated. 

[Nice Guy Eddie 13539]

XLOOKUP

[Dahobbs 17803]

2 hours ago, Crapinon said:

I need some help with Excel functions. I have to update pricing on our website by pulling list price from vendor supplied price list. I can get the website content list sent to me in Excel. I would like to set the website content Excel sheet to find part numbers on Excel sheets supplied by our vendors, then import the list price for each item from that sheet to our website sheet. It seems like it should be easy to do but I've searched all day trying to figure out how to do it. I'm sure I'm not putting the correct terms in the search engine. Any help or direction to help would be appreciated. 

You'll either use vlookup/xlookup or an index match combination or a sum product combination. It'll all depend on the exact format of the spreadsheet and what you're trying to to accomplish. If you can post a sample, happy to take a look. 

[Crapinon 2311]

I'll try to get e screenshot but basically what I'm trying to do is this: We have a master sheet of our web products on sheet 1 of an excel file, in alphabetical order by part number. Column A contains the part numbers, B the description, C UOM, D Main category location and E secondary category location.

I imported our vendors spread sheet on sheet 2 of the same excel file. On that sheet (2) column A is part number, column B Description and Column C is the list price. 

I would like to get sheet 1, column A to find the corresponding part number on sheet 2, column A and bring the list price from sheet 2, to a new column F on sheet 1.

Not all the part numbers are from the same vendor so I thought I could import each different vendor price list to a different sheet and repeat the process. I tried using the VLOOKUP function, but the formulas I found on the internet wouldn't work the way I wanted and always returned a null value or "incomplete formula". Thanks again for the help!

[Dahobbs 17803]

19 minutes ago, Crapinon said:

I'll try to get e screenshot but basically what I'm trying to do is this: We have a master sheet of our web products on sheet 1 of an excel file, in alphabetical order by part number. Column A contains the part numbers, B the description, C UOM, D Main category location and E secondary category location.

I imported our vendors spread sheet on sheet 2 of the same excel file. On that sheet (2) column A is part number, column B Description and Column C is the list price. 

I would like to get sheet 1, column A to find the corresponding part number on sheet 2, column A and bring the list price from sheet 2, to a new column F on sheet 1.

Not all the part numbers are from the same vendor so I thought I could import each different vendor price list to a different sheet and repeat the process. I tried using the VLOOKUP function, but the formulas I found on the internet wouldn't work the way I wanted and always returned a null value or "incomplete formula". Thanks again for the help!

My main concern would be that your vendor part list may have duplicates of parts numbers for some reason or another. Personally, I've always used Index Match rather than a lookup function because it is more flexible. Frankly, I wasn't aware of xlookup until recently and haven't played with it. But my understanding is that it addresses some of the limitations of vlookup. 

Anyway, based on your description, it would look something like this:

=index(table2,match([@[part number]], table2[part number],0),3)

First, I'm assuming you've turned your sheets from ranges into a table (select the data and press control 't'). There are a few reasons to do this, including that it prevents sorting from fucking up your data and that it makes these formulas easier and more readable.

I'm also assuming the Vendor sheet table is named table2. And I'm assuming your web products sheet is also a table, but the name isn't important for the formula.

Finally, I'm assuming the name you provided above for the columns is the actual column header. If it isn't, just adjust the formula to match the header column wording. 

This formula compares the part number in your web product sheet to the part number list in the vendor sheet. When it finds an exact match, it outputs the third column (list price) from your vendor sheet. 

[Gene Parmesan 4022]

I've never heard of xlookup.  I am a big vlookup guy, what does X do better?

[Apep 645]

You don’t have to count columns like you do with vlookup. It’s similar to sumif in how it is set up. 

[Dahobbs 17803]

56 minutes ago, Gene Parmesan said:

I've never heard of xlookup.  I am a big vlookup guy, what does X do better?

Vlookup has issues if data isn't sorted. It requires the searching column to be the far left. It breaks if a column is added to an array. It does exact match searches only. It can't search horizontally. It has a fairly low character limit. It works fine when it works. But it can be finicky and break in a lot of ways people don't expect. Xlookup appears to fix some of these issues. index match or sumproduct are more flexible. If you're looking to do anything more complicated, they are good to learn. 

[Crapinon 2311]

17 hours ago, Dahobbs said:

My main concern would be that your vendor part list may have duplicates of parts numbers for some reason or another. Personally, I've always used Index Match rather than a lookup function because it is more flexible. Frankly, I wasn't aware of xlookup until recently and haven't played with it. But my understanding is that it addresses some of the limitations of vlookup. 

Anyway, based on your description, it would look something like this:

=index(table2,match([@[part number]], table2[part number],0),3)

First, I'm assuming you've turned your sheets from ranges into a table (select the data and press control 't'). There are a few reasons to do this, including that it prevents sorting from fucking up your data and that it makes these formulas easier and more readable.

I'm also assuming the Vendor sheet table is named table2. And I'm assuming your web products sheet is also a table, but the name isn't important for the formula.

Finally, I'm assuming the name you provided above for the columns is the actual column header. If it isn't, just adjust the formula to match the header column wording. 

This formula compares the part number in your web product sheet to the part number list in the vendor sheet. When it finds an exact match, it outputs the third column (list price) from your vendor sheet. 

Thanks! I'll play with it this afternoon. Duplicate parts numbers will be almost nil. I'll let you know how it works and thanks again! 

Edited May 1 by Crapinon
spelling

[Dahobbs 17803]

2 minutes ago, Crapinon said:

Thanks! I'll play with it this afternoon. Duplicate parts numbers will be almost nil. I'll let you know how it works and thanks again! 

Good deal. If you need additional help, post here or PM me. I'm happy to look at the sheet if you want to make it available on google drive or something. 

btw, I've researched xlookup now and does appear to address all my concerns with vlookup. Good little function that you could also use for this.  

[Crapinon 2311]

OK, I've figured xlookup out with one caveat. The part numbers on our website master sheet have our vendor prefix before the part number. Our vendor price list do not. So in sheet 1 the part number is SK-A-050 and on sheet 2 it's A-050 (no prefix). I've tried "*"&{column/cell}&"*" to see if it would just pick up the A-050 but it doesn't work.

If I add the SK- to sheet 2 part numbers, it works perfect. But I've got 1000's of parts with different prefixes and can't add to all of them.  Is there anyway to have xlookup look for a partial match in the part numbers, no 2 letter prefix?

[Gene Parmesan 4022]

I've never used lookups for anything other than an exact match.  If it's always a 2 letter prefix, text to columns can lop off that off for you.

[DallasHorn26 586]

Yeah, a text to column to separate the pre-fix out would be easiest. There may be a way to incorprate the "Right" formula to where it only searches by the "right" 5 characters, if the format for the part numbers and prefixes are all the same number of characters.  Example being: =Right(SK-A-050,5) will return A-050.

 

 

[Dahobbs 17803]

2 hours ago, Crapinon said:

OK, I've figured xlookup out with one caveat. The part numbers on our website master sheet have our vendor prefix before the part number. Our vendor price list do not. So in sheet 1 the part number is SK-A-050 and on sheet 2 it's A-050 (no prefix). I've tried "*"&{column/cell}&"*" to see if it would just pick up the A-050 but it doesn't work.

If I add the SK- to sheet 2 part numbers, it works perfect. But I've got 1000's of parts with different prefixes and can't add to all of them.  Is there anyway to have xlookup look for a partial match in the part numbers, no 2 letter prefix?

You need to set matchmode to 2 in order to use wildcards with xlookup. 

[Dahobbs 17803]

56 minutes ago, Dahobbs said:

You need to set matchmode to 2 in order to use wildcards with xlookup. 

To be clear, matchmode is the second to last term in the function.

=Xlookup(lookup term, lookup array, lookup return, if no match, matchmode, search mode)

[Nice Guy Eddie 13539]

On 4/30/2024 at 5:37 PM, Gene Parmesan said:

I've never heard of xlookup.  I am a big vlookup guy, what does X do better?

 

On 4/30/2024 at 5:43 PM, Apep said:

You don’t have to count columns like you do with vlookup. It’s similar to sumif in how it is set up. 

And you can add error handling right in the formula. Basically Microsoft fixed Vlookup known limitations with Xlookup. VLOOKUP still works though.

[Crapinon 2311]

13 hours ago, Dahobbs said:

To be clear, matchmode is the second to last term in the function.

=Xlookup(lookup term, lookup array, lookup return, if no match, matchmode, search mode)

I had tried that, adding ,2,1 to the end of the formula. But it is returning the wrong info. The search criteria is on sheet 1 is SK-A-050. I would like it to find A-050 on sheet 2. But the info it finds on sheet 2 is SET-900009. I thought it went to the first "S" it found but there are other parts that begin with S before it. I found this on a Microsoft page but I can't figure out how to put it in the formula:

Tips: 

You can use wildcard characters — question mark (?), asterisk (*), tilde (~) — in your search criteria.

Use the question mark (?) to find any single character — for example, s?t finds "sat" and "set".

Use the asterisk (*) to find any number of characters — for example, s*d finds "sad" and "started".

Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters  — for example, fy91~? finds "fy91?".

[Dahobbs 17803]

41 minutes ago, Crapinon said:

I had tried that, adding ,2,1 to the end of the formula. But it is returning the wrong info. The search criteria is on sheet 1 is SK-A-050. I would like it to find A-050 on sheet 2. But the info it finds on sheet 2 is SET-900009. I thought it went to the first "S" it found but there are other parts that begin with S before it. I found this on a Microsoft page but I can't figure out how to put it in the formula:

Tips: 

You can use wildcard characters — question mark (?), asterisk (*), tilde (~) — in your search criteria.

Use the question mark (?) to find any single character — for example, s?t finds "sat" and "set".

Use the asterisk (*) to find any number of characters — for example, s*d finds "sad" and "started".

Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters  — for example, fy91~? finds "fy91?".

I really need to see the formula and potentially the sheets to adequately diagnose this. Just based upon your description, if your search criteria is "SK-A-050", then your listing on sheet 2 needs to contain "SK-A-050". You can add a wildcard to the lookup_value and search for additional items that match the lookup value + something else. For example, if you list "*" & "SK-A-050" as your look up value, it would find "MT-SK-A-050" as a match. But you cannot add a wildcard to the lookup_array that you're searching in, meaning you can't search for "SK-A-050" and find a listing of "A-050".  It also seems like there is something off with your search_array and return_array. Based on your description, SET_900009 would not be a possible return value absent something else funky going on. 

You may actually be better off first using an xlookup on the vendor sheet to match to your master AND then doing another lookup on the master using that value as your search array. However, I do have some concerns that your data may contain multiple matches using that methodology. It really just depends on the precise format of the data, which is hard to tell from your posts. 

Further, if the prefix added by your company to the vendor list is predictable, meaning it is always a set number of characters, you can use the "MID" function nested within an xlookup. Using your SK-A-050 example above, the formula would look like this:  =XLOOKUP(MID([@item],4,LEN([@Item]),table2[item],table2[price],"",0,1). 

Again, if you want to share a sample sheet, even with just dummy data, I can probably help more. 

Edited May 8 by Dahobbs

[Crapinon 2311]

21 hours ago, Dahobbs said:

I really need to see the formula and potentially the sheets to adequately diagnose this. Just based upon your description, if your search criteria is "SK-A-050", then your listing on sheet 2 needs to contain "SK-A-050". You can add a wildcard to the lookup_value and search for additional items that match the lookup value + something else. For example, if you list "*" & "SK-A-050" as your look up value, it would find "MT-SK-A-050" as a match. But you cannot add a wildcard to the lookup_array that you're searching in, meaning you can't search for "SK-A-050" and find a listing of "A-050".  It also seems like there is something off with your search_array and return_array. Based on your description, SET_900009 would not be a possible return value absent something else funky going on. 

You may actually be better off first using an xlookup on the vendor sheet to match to your master AND then doing another lookup on the master using that value as your search array. However, I do have some concerns that your data may contain multiple matches using that methodology. It really just depends on the precise format of the data, which is hard to tell from your posts. 

Further, if the prefix added by your company to the vendor list is predictable, meaning it is always a set number of characters, you can use the "MID" function nested within an xlookup. Using your SK-A-050 example above, the formula would look like this:  =XLOOKUP(MID([@item],4,LEN([@Item]),table2[item],table2[price],"",0,1). 

Again, if you want to share a sample sheet, even with just dummy data, I can probably help more. 

I was able to get a spreadsheet with both sets of part numbers. Now it will work like I need it to work. Thanks for the help in finding the correct functions. XLOOKUP works great.

[NWBuck 8590]

OK, I'll play... 

Excel sheet with a macro to datestamp when a cell in the row changes. 

Can't get it to fire when a cell auto-fills in response to another sheet being updated (using xlookup). So, the cell isn't empty (per se) since it's got that formula in it. But I'd like it to datestamp when it goes from blank to showing data. 

Thoughts? 

[Dahobbs 17803]

6 hours ago, NWBuck said:

OK, I'll play... 

Excel sheet with a macro to datestamp when a cell in the row changes. 

Can't get it to fire when a cell auto-fills in response to another sheet being updated (using xlookup). So, the cell isn't empty (per se) since it's got that formula in it. But I'd like it to datestamp when it goes from blank to showing data. 

Thoughts? 

Post the code. 

[NWBuck 8590]

4 hours ago, Dahobbs said:

Post the code. 

Borrowed from an online forum, adapted for my parameters:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' define Timestamp output column
    Const TimeStampColumn As String = "I"
    
    ' Observe C2:E1048576 for changes
    Dim RangeToObserve As Range
    Set RangeToObserve = Me.Range("A3:E" & Me.Rows.Count)
    
    ' Get all changed cells within the observed range
    Dim ChangedCells As Range
    Set ChangedCells = Application.Intersect(Target, RangeToObserve)
    
    If Not ChangedCells Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo ENABLE_EVENTS ' make sure in case of errors events get enabled!
        
        Dim Row As Variant ' write timestamp for every changed row
        For Each Row In ChangedCells.Rows
            With Me.Cells(Row.Row, TimeStampColumn)
                .Value = Now()
                .NumberFormat = "dd\/mm\/yyyy hh:mm"
            End With
        Next Row
        
        On Error GoTo 0
    End If
    
ENABLE_EVENTS:
    Application.EnableEvents = True

    If Err Then
        ' make sure to raise the error otherwise it will be silent and you will never notice that anything went wrong.
        Err.Raise Err.Number
    End If
End Sub

[Dahobbs 17803]

3 hours ago, NWBuck said:

Borrowed from an online forum, adapted for my parameters:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' define Timestamp output column
    Const TimeStampColumn As String = "I"
    
    ' Observe C2:E1048576 for changes
    Dim RangeToObserve As Range
    Set RangeToObserve = Me.Range("A3:E" & Me.Rows.Count)
    
    ' Get all changed cells within the observed range
    Dim ChangedCells As Range
    Set ChangedCells = Application.Intersect(Target, RangeToObserve)
    
    If Not ChangedCells Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo ENABLE_EVENTS ' make sure in case of errors events get enabled!
        
        Dim Row As Variant ' write timestamp for every changed row
        For Each Row In ChangedCells.Rows
            With Me.Cells(Row.Row, TimeStampColumn)
                .Value = Now()
                .NumberFormat = "dd\/mm\/yyyy hh:mm"
            End With
        Next Row
        
        On Error GoTo 0
    End If
    
ENABLE_EVENTS:
    Application.EnableEvents = True

    If Err Then
        ' make sure to raise the error otherwise it will be silent and you will never notice that anything went wrong.
        Err.Raise Err.Number
    End If
End Sub

This is tough. N.B. I know just enough VBA to be dangerous. That said, I'm pretty sure that code will not update based upon a re-calculation of a cell. This is really something you want a proper database with an audit trail to do and not a spreadsheet. The only way I can think of to do what you're wanting is to modify the same code above to also track the reference points for the range you're tracking. You'll need to trace that back to whatever reference requires manual input, as this code will only get activated with a manual change.  You also might be able to do this with a formula if you enable circular functions/iterations, but I'm not sure if it will work on re-calculations. 

Edited May 10 by Dahobbs

[NWBuck 8590]

OK, @Dahobbs, new question...

I've got a database where folks are filling in information, but there are a lot of categories that are blank. (Specifically, we're hiring, and my database shows all the positions- my staff updates demographic info when the positions are filled)

I've got another table which pulls out limited info (name, position, pay rate), and I'd prefer that Finance wouldn't have to wade through all the blank rows (open positions).

Is there a way to create a table that hides rows if one cell (last name, for instance) is empty/blank and then unhides the row if that cell gets updated?

I'll hang up an listen.

[Dahobbs 17803]

On 5/11/2024 at 3:56 PM, NWBuck said:

OK, @Dahobbs, new question...

I've got a database where folks are filling in information, but there are a lot of categories that are blank. (Specifically, we're hiring, and my database shows all the positions- my staff updates demographic info when the positions are filled)

I've got another table which pulls out limited info (name, position, pay rate), and I'd prefer that Finance wouldn't have to wade through all the blank rows (open positions).

Is there a way to create a table that hides rows if one cell (last name, for instance) is empty/blank and then unhides the row if that cell gets updated?

I'll hang up an listen.

Sorry, I just saw this. You're trying to do with with excel? You can use the filter function to do what you're asking (or Finance could filter out blanks themselves). 

Format is: =filter(array, include criteria, optional if empty return)

=filter(MasterTable([[Position]:[PayRate]],MasterTable[Lastname]<>"")

The example above would show everything from a table named MasterTable between the Position column and the PayRate column when the Lastname column is not blank. The formula returns an array, so it'll fill in everything for you.  You can modify this to also filter on multiple criteria using the following format for Include Criteria:

Match All

(MasterTable[Lastname]<>"")*(MasterTable[FirstName]<>"")

Match Any

(MasterTable[Lastname]<>"")+(MasterTable[FirstName]<>"")

The first one returns anything where both firstname and lastname are not blank. The second one returns where either firstname or lastname is not blank.

 

Edited May 14 by Dahobbs