Jump to content

Excel help


Recommended Posts

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. 

Link to comment
Share on other sites

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. 

  • Hook 'Em 1
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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. 

  • Hook 'Em 1
Link to comment
Share on other sites

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. 

  • Hook 'Em 2
Link to comment
Share on other sites

Posted (edited)
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 by Crapinon
spelling
Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?".

Link to comment
Share on other sites

Posted (edited)
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 by Dahobbs
  • Hook 'Em 1
Link to comment
Share on other sites

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.

  • Hook 'Em 2
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Posted (edited)
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 by Dahobbs
  • Hook 'Em 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Posted (edited)
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 by Dahobbs
  • Hook 'Em 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...