Mr Excel & excelisfun Trick 32: Three Methods for 2-Way Look

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

Hi there,

I have the below code. It takes data from a table within excel and then adds commas so i then can pasted within the body of an email and then highligt and insert a table. My code displays the email but I get an error when it attempts to highlight. I have used the recorded within outlook and I am using the code from the recorder. I am thinking all i need to do is define the object first and then it will work but thats the part i dont understand. I am posting the whole code below however, there is some extra since the code was originally intended to convert the table to html.

BTW, the table I am creating is much larger, i am only testing with the below to make the code less.

Code:

Sub email()

'=======================================================================================
'this is the email output to use.
'==========================================================================================


Dim TodayFile
Dim FileDate

'Sends a specified range to a Outlook message and retains Excel formatting

'References needed :
'Microsoft Outlook Object Library := msoutl9olb
'Microsoft Scripting Runtime      := SCRRUN.DLL

'Dimension variables
'// ie. if Ol not installed then we need to Late bind & define as Obj
'// Use Late binding > Outlook.Application > olMail As Outlook.MailItem
'// Plus define olmailItem
Dim olApp As Object, olMail, olMailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String


'Select the range to be sent
Set rngeSend = Range("B3:J29")

'Now create the HTML file
'// Changes by IFM
'// changed "C:\temp\sht.htm" to Dynamically get Sys Temp Dir
'// To cover the instance where Tmp Dir is NOT @ C:\
Dim SysTmp As String
SysTmp = TmpFolderLocation
ActiveWorkbook.PublishObjects.Add(xlSourceRange, SysTmp & "\sht.htm", _
    rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True

'Create an instance of Outlook (or use existing instance if it already exists
Set olApp = CreateObject("Outlook.Application")

'Create a mail item
Set olMail = olApp.CreateItem(olMailItem)

'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile(SysTmp & "\sht.htm", ForReading)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = TStream.ReadAll

Worksheets("SumPasteTab").Activate
TodayFile = Range("Q27").Value
FileDate = Range("N2").Value

attachmnt = "S:\USA-HOUSTON\CPDS\GT FAMM\IctsOnline\Global Trading\Risk Control\Daily GM Tracking\test\" & TodayFile & ".pdf"



'flat
F_CSAT = Range("F15").Value
F_PSAT = Range("F16").Value
F_LSAT = Range("F17").Value
F_SOG = Range("F18").Value
F_TTL = Range("F19").Value

'utilization
FU_CSAT = Range("G15").Value
FU_PSAT = Range("G16").Value
FU_LSAT = Range("G17").Value
FU_SOG = Range("G18").Value
FU_TTL = Range("G19").Value



strbody = FU_CSAT & "," & FU_PSAT & "," & FU_TTL & Chr(13) & _
F_CSAT & "," & F_PSAT & "," & F_TTL & Chr(13) & _
U_CSAT & "," & U_PSAT & "," & U_TTL & Chr(13)


With olMail
    .To = "hlcu@chevron.com"
    .Subject = " Global DPR  " & Format(Date, "mm-dd-yyyy")
    .Body = strbody
    '.HTMLBody = strHTMLBody
    '.Attachments.Add (attachmnt)
    .Display
    '.Send
End With


    Selection.HomeKey Unit:=wdStory
    Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend
    Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=3, _
        NumRows:=3, AutoFitBehavior:=wdAutoFitFixed
        With Selection.Tables(1)
            If .Style  "Table Grid" Then
                .Style = "Table Grid"
            End If
            .ApplyStyleHeadingRows = True
            .ApplyStyleLastRow = True
            .ApplyStyleFirstColumn = True
            .ApplyStyleLastColumn = True
        End With


     
End Sub

I am having trouble creating a similar spreadsheet to Excelisfun video #712. The only difference between mine and his, is that I have 3 blanks between the data sets. Can someone help?

http://www.youtube.com/user/ExcelIsF...22/Po35KC2ODNw

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

Hi,
I'm new to excel, but i've been working with java for a long time, therefore my logic is built with java in mind, i cant seem to find functions that will solve my problems. please help me do this, I've reached a wall, and do not know how to proceed.

Simple summary of the model,
1. Doctors have core and secondary competencies, and shifts.
2. Patients have illnesses(which is correspondent to doctors competencies), the time they walk in, (also categorized in shifts)
3. Core competencies are faster to do than secondary

So i need to effectively plot a "most optimized" way where doctors can treat the patients

The logic is-
a. Look at the the patients illness,
b. find the doctor with the core competency which corresponds with the patients illness
c. if doctor with core competency do not correspond, look at secondary competency
d. check whether doctor is free, if free- assign, else find next doctor
e. once doctor is assigned, find the amount of time needed (in another table)
f. book the doctor in the table

I know it sounds complicated, but look at the table attached, it would seem so much easier

click right he http://www.excelforum.com/attachment...1&d=1300781256