Check for value in an Excel list and insert if not present 1

[spartain]

I'm sorry if this gets a little long, but I want to provide enough detail to adequately communicate my question:

I have two Excel tables located on different worksheets. The first table is a table that contains order information by product SKU. The other table contains production information, also by SKU. I have the tables linked so that the production table looks up order quantities for different SKUs from the order table using a "Sumif" command and the SKU column.

In order for the tables to work correctly, the production table has to have an exhaustive list of SKU's--if a new SKU is entered in the order table, it must be manually entered into the production table to be accounted for. I would like to automate this process so that:
1. Excel reads a SKU value in the order table
2. Checks for a matching value in the production table
3. If a match is not found, inserts a new row containing the SKU into the production table. If a match is found, nothing is done.

Any suggestions here would be greatly appreciated.
Thanks!

 

[spartain]

xlbo & ATylman,
I received an e-mail that said you both responded to my question (Thank you!), but I can't seem to find the responses...

My first post may have been to confusing, so I'll try to be a little more clear.
Let's say my two tables look like the following:

TABLE #1 ("Production table" from first posting)
FRUIT TOTAL
Apple 1
Orange 3
Lemon 3
Lime 6

TABLE #2 ("Order table" from first posting)
ORDER QTY
Apple 1
Orange 2
Lemon 3
Orange 1
Lime 4
Lime 2

Where the "TOTAL" column in TABLE #1 is linked to TABLE #2 using the "Sumif" command:
=Sumif('TABLE2'!ORDER,FRUIT,'TABLE2'!QTY)

The problem is that if the order in TABLE #2 is for a fruit type that is not listed in TABLE #1, it will not be missed by the Sumif command. So, I would like to be able to do two things:

1. When an order is entered in TABLE #2 (e.g. "Kiwi 4&quot, Excel will check for a corresponding "FRUIT" type in TABLE #1.
2. If the fruit type does not exist in TABLE #1, a macro fires that will insert that fruit type into TABLE #1.

I would REALLY like some help on this. I'm a little new at programming macros, and have been impressed by the level of expertise I've seen on this site.
Thanks again!

 

[ATylman]

Hi Spartain

I did reply & said that it would take some (simple) coding so I suggested that we move the assistance 'offline' and supplied an email address. Perhaps this is a no-no in this forum?
Anyway, drop me a line at atylman@webone.com.au as this sort of thing needs some specifics to work (but is possible and you can bodgy the data so no commercial in confidence stuff is revealed.)

 

[ATylman]

Hi Spartain

Well it's not pretty but it does work. Hope your macro skills are up to adjusting this to suit your exact needs!
This macro will need to be run after entering a batch of orders.

Sub TekTip()
'
' TekTip Macro
' Macro written 23/06/2003 by Atylman
'
'Declares variables

Dim rw As Integer
Dim rw2 As Integer
Dim totalrw As Integer
Dim fruit As String

'Sets initial variable value
rw = 1
rw2 = 2

'Sets Range as may have changed
  
    Sheets("Order").Select
    'counts total rows in Order Sheet and sets totalrw value
    Do While Cells(rw, 1) <> &quot;&quot;
        rw = rw + 1
    Loop
    
    totalrw = rw - 1
        
    'uses total rows to set ranges
    ActiveWorkbook.Names.Add Name:=&quot;Order&quot;, RefersToR1C1:=&quot;=Order!R1C1:R&quot; & totalrw & &quot;C1&quot;
    ActiveWorkbook.Names.Add Name:=&quot;Qty&quot;, RefersToR1C1:=&quot;=Order!R1C2:R&quot; & totalrw & &quot;C2&quot;
    
    'This section takes each fruit on Order sheet in turn and adds to Production sheet if not present
    
    'sets variable
    rw = 2
    'loops until end of list
    Do While Cells(rw, 1) <> &quot;&quot;
        'sets fruit variable
        fruit = Cells(rw, 1)
        Sheets(&quot;Production&quot;).Select
        'loops until end of list
        Do While Cells(rw2, 1) <> &quot;&quot;
            'checks for matching fruit and if found exits loop
            If (Cells(rw2, 1)) = fruit Then
                rw2 = 2
                Exit Do
            ' checks for end of list (meaning fruit has not been found) and adds fruit to production
            Else
                If (Cells(rw2 + 1, 1) = &quot;&quot;) Then
                    Cells(rw2 + 1, 1) = fruit
                    Cells(rw2 + 1, 2) = &quot;=SUMIF(Order,A&quot; & rw2 + 1 & &quot;,Qty)&quot;
                    rw2 = 1
                End If
                rw2 = rw2 + 1
                              
            End If
        Loop

        rw = rw + 1
        Sheets(&quot;Order&quot;).Select
    Loop
    
    
End Sub

 

[xlbo]

ATylman - your thread was probably deleted because this is an online forum and your 1st post suggested emailing you offline to resolve the issue. I know where you are coming from - sometimes it is easier to solve the problem when you can see it in front of you but that is not the way these fora work. The whole point of them is to share technical knowledge and if the problem is resolved via email, it can mean that no-one else gets to see the resolution. Please only suggest offline resolution as a last resort

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[spartain]

ATylman,
Thanks for your help!
(I'll let you know how it works, or if I have any questions)

 

[spartain]

ATylman,
Works great! Thanks!

BTW, xlbo...what happened to your response?

 

[xlbo]

That got deleted as well beacuse I was referencing the fact that the post would probably be deleted for taking the thread offline too soon.
ps - the TekTips way of saying thanks is to award a star. This also helps people who search the archives to narrow down their search to those posts that have helpful ideas / resolve the thread. You can do this by clicking on the "Mark this post as a helpful / expert post" link at the bottom of the appropriate thread

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[xlbo]

ooops - I must've been writing that post whilst you were awarding a star then

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[spartain]

Hey,
I have one more question I'd like some help with...
The above code from ATylman works, but is slightly inefficient for the size of list I need to use it for. The reason is that every time it is run, it checks the entire list of "Orders" (which can take quite a bit of time, given an order list of 500+).
What I tried to do with it is set the basic "fruit-checking" portion of the code inside of a custom Function in Excel. The function is called for each individual order as they are entered into the list, passing the fruit type as an argument. The function code is listed below:

Function SinglefruitCheck(fruit)

'Declare variables
Dim row As Integer

'Sets initial variable value
row = 2

Sheets("Production&quot.Select
'loops until end of list
Do While Cells(row, 1) <> &quot;&quot;
'checks for matching fruit type and if found exits loop
If Cells(row, 1) = fruit Then
Exit Do
'checks for end of list (meaning fruit has not been found) and adds fruit to production
Else
If (Cells(row + 1, 1) = &quot;&quot Then
Cells(row + 1, 1) = fruit
row = row + 1
Cells(row + 1, 2) = &quot;=SUMIF(Order,A&quot; & row + 1 & &quot;,Qty)&quot;
End If
row = row + 1
End If
Loop

End Function


The problem is...well, it doesn't work. I've played with it for awhile, and am completely at a loss.
Got any ideas??

 

[ATylman]

Spartain

First off try changing the ROW variable to RW as Row is a restricted name.

Atylman

 

[spartain]

Okay, did that. Still doesn't work.

It appears that the function is comparing the value in &quot;fruit&quot; to the Order list on the Order sheet (where the fruit value is coming from), NOT the production list (even though the code selects the production sheet). The result is that the function ALWAYS finds a match, because eventually it compares the fruit value to itself.

It also seems like the function is unable to insert values using the &quot;Cells&quot; command. I tried a simple version of the function, just to insert a value into another cell on the same page:

Function Insertwhatever(whatever)

Sheets(&quot;Order&quot.Select
Cells(10, 6) = whatever

End Function

Which also does not work.

 

[ATylman]

Spartain
Sorry, I've never used the Function side of things with macros so I don't think I'll be much help. That being said I noticed a couple of errors in the codes

If (Cells(row + 1, 1) = "") Then
                    Cells(row + 1, 1) = fruit
                    Cells(row + 1, 2) = "=SUMIF(Order,A" & row + 1 & ",Qty)"
                 row = row + 1
                 End If
                 row = row + 1
             End If
        Loop[\Code]

[Code]
Function Insertwhatever(whatever)

    Sheets("Order").Select
    Cells(10, 6) = "whatever"

End Function
[\Code]