HLOOKUP, if,? OR MACRO

[bobbyarnold]

hi everyone, Newbie here. so please take it easy on me.



I have a very real issue and dont know how to get the function or macro I need. I am also working with Access but right now need the help with Excel 07.

the problem

I have a workbook, 5 Columns A: Weld ID, B:Welder 1start, C:Welder 2start, D:Welder 3finish, E:Welder 4finish. and thousands of rows
i need self population and repeatability,

I have numerous weld ID's and Numerous welders but no more than 4 welders per weld and as little as 1 welder per weld, the problem is trying to count how many welds each welder made, if four welders made a weld
I want to give each of them credit with 1 weld but if only 1 welder worked on the weld only give credit for 1 weld, but the kicker his or her id is (example) 1234A must apper in B, D column at minimum these this represents different stages of the weld beginning and finish.
So to sum it up if 2 welders begin a weld and only 1 guy finished it all three need credit, but if same guy began and finished only credit 1, I will
tally all welds beside the welderID. It does not have to grab the out of first available as long as it gives credit to the welder as long as the welder id is in one of the four blocks. for each weld. any help would be greatly appreciated and i await your replies. thanks in advance.

[macropod]

Hi Bobby,

With your raw data in Sheet 1 and the list of welders in Sheet2 (col A), you could use the following macro:

Sub Summarize()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long
Dim StrName As String, iTally As Long
With ThisWorkbook.Sheets("Sheet2")
  For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
    StrName = .Range("A" & i).Value
    iTally = 0
    With ThisWorkbook.Sheets("Sheet1")
      For j = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        For k = 2 To .Cells.SpecialCells(xlCellTypeLastCell).Column
          If .Cells(j, k).Value = StrName Then
            iTally = iTally + 1
            Exit For
          End If
        Next
      Next
    End With
    .Range("B" & i).Value = iTally
  Next
End With
Application.ScreenUpdating = True
End Sub

[bobbyarnold]

this macro works flawlessly, I do have one more request though, what i would like is layman description of how the syntax works, B/C what i really have is appr. 35 columns and my weld id is actually in lets say column H and welders 1 through 4 are in other colums, also i would like to apply this to multiple tabs b/c i have lets say superheat welds tab1, waterwall welds tab2 , economizer welds tab3. i would like to keep total # of welds per welder in a dif. column, this way i can calculate reject rate per location per welder( i can handle the rej rate), but same tab if possible. seperate tabs if need be. is this possible? if not is this type of function aval. in access? and again thankk you very much for your work i greatly appreciate it. btw on each tab my weld id and welder stencils are in matching columns(ie...weld id on each tab is col. H)

[macropod]

Hi Bobby,

Here's a commented version of the sub. These should help you understand what's going on.

The code doesn't look at the Weld ID at all. If you were to name your various worksheets with the Weld IDs, then another loop could be set up to go through those worksheets and, based on their names, put the output data in the corresponding columns on the 'summary' sheet. Or you could stick with what you've got and have the code look at the Weld IDs in Column A of each sheet. Either way, additional logic and looping will be required for the code to work with multiple tabs.

Sub Summarize()
'Disable ScreenUpdating for speed
Application.ScreenUpdating = False
'Define variables
Dim i As Long, j As Long, k As Long
Dim StrName As String, iTally As Long
'Start with the list of welders on Sheet2
With ThisWorkbook.Sheets("Sheet2")
  'Get each used row on Sheet2
  '.Range("A" & .Rows.Count).End(xlUp).Row locates the last row
  For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
    'Store the welders' name
    StrName = .Range("A" & i).Value
    'Set the weld count to 0
    iTally = 0
    'Look in Sheet1
    With ThisWorkbook.Sheets("Sheet1")
    'Get each used row on Sheet1
    '.Range("A" & .Rows.Count).End(xlUp).Row  locates the last row
      For j = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        'Get each used column on Sheet1
        '.Cells.SpecialCells(xlCellTypeLastCell).Column  locates the last column
        For k = 2 To .Cells.SpecialCells(xlCellTypeLastCell).Column
          'Test whether the cell for the current row/column matches the welder's name
          If .Cells(j, k).Value = StrName Then
            'If it is, increment the tally, then skip the rest of this row
            iTally = iTally + 1
            Exit For
          End If
        Next k 'Check the next column for this row
      Next j 'Check the next row
    End With
    'update the welder's tally count
    .Range("B" & i).Value = iTally
  Next i 'Get the next welder
End With
'Restore ScreenUpdating
Application.ScreenUpdating = True
End Sub

[bobbyarnold]

Thank you macropod this works like a charm, i couldn't have done it without you!

[bobbyarnold]

Ok now i have this Macro working for me and doing alotALOT! of my long hand work. Was wondering if there is a way to count the welders for each weld (Done) and couunt if there is a date in a cell in the row from which it counts. so if a welder gets credit for 1 weld inwhich his ID is in a cell in that row. could there be a function or macro that would count if the row has a date in a coulumn. example the welder has 20 welds (Done) if 15 rows have been x-rayed then the number beside 20 welds is 15 for the number of xrayed welds. thanks in advance for any help or communication
if this cant be done or is too complex just let me know but this would be of great benefit as well.

[macropod]

Hi Bobby,

Yes, all of that could be done. But I haven't seen anything in the workbook you posted earlier that provides a column for such dates to be tested (or for the different kinds of welds).

Somehow, I suspect this project is getting a serious case of scope creep

[bobbyarnold]

Hope this helps, This is my test version from yesterday i have a good working copy @ work and i made five seperate macros BC they seem to freeze MS office for just a minute before finishing their process. also now with this i am gettin a debug issue while running seems to have something to do with the Integer i,J,K had a couple issues at first now my work copy works great if you prefer i can send it tomorrow. just so you know I would like to put the number xrayed between columns, B-C, D-E, ect. the date block will be AG column titled ACC. any help would be greatly appreciated. I had to seriously trim some fat to get this to upload.

[macropod]

Hi Bobby,

The WB you posted has numerous corrupt worksheet references, apparently due to bits & pieces being deleted. That's the most likely cause of the vba errors you're getting. You may as well delete that WB and wait till you've got a good one to post. I suggest deleting any personal info (eg names) too.

[bobbyarnold]

again i had to trim some fat so i could upload, Disregard the good bad columns that is how another guys tracks whether the welder ((stamp,stencil,ID) all the same thing) is on the list basically data validation but i do a lookup table and will install start running it later.please refer to earlier email. for the info I am trying to get out. of the spread sheet. And again thanks for any help believe me i could not do it alone.

[macropod]

Hi Bobby,

Your latest workbook doesn't seem to have any welder ID data on the various worksheets from which to compile the stats. Also, I need clarification of one issue:
• You say "I would like to put the number xrayed between columns, B-C, D-E, ect." You can't put anything between columns - data must go in columns. I assume these new data are to go onto your 'WelderTotals' worksheet. If they're not going into the existing Columns C, E, G, etc 9which look like their meant for the simple counts of welds, then you're going to need new columns for the new data. These could be inserted before/after each of the existing columns, or as a group somewhere after Column P (the existing Column P could be moved to suit)

[bobbyarnold]

The Macro(s) are checking Stamps from "welder Totals" tab Stamp column,
on all other tabs its looking in the RootStencil# and CapStencil# columns. The ACC. Column is where the date is that i wanna pull from to verifiy how many have been wrayed if there is a date in the ACC column then xray is "1" per row. Im not a techie hope this is helping I hid all columns not important for this particular process and will upload a new Excel WB. I changed the "welder Totals" Rej columns to accept the info im looking for Total Xrayed just let me know if this helps. Thanks in advance.

[macropod]

Hi Bobby,

Try the attached. I've:
• added extra columns on the 'Welder Totals' sheet to hold the 'ACC' data for each sheet
• made the column heading the same as the sheet names (important for the re-coded macro - see below)
• simplified your 'Rej' formulae so you don't have to know how many row on each sheet have been used
• unhidden all columns on all sheets (just so I could understand each sheet's structure
• recoded the macro to start checking on row 3 of each welding sheet and to only check for welders' ID in columns 21-30, in steps of 3. These mods will greatly speed up execution.
• added a progress report on the status bar
• added the looping and extra logic to the sub so that all welding sheets can be processed in the one sub. These mods include the requirement that the sheet names match the column names on the 'Welder Totals' sheet.

[bobbyarnold]

the Superheat Header, & Econ Supply Tube tabs are not calculating and this macro i couldnt decipher what tells it which tab to start and finish. Welder (2707A) Should read on all "1's" on "welder totals" everything else seems to be fine. Can i just import this macro and welder totals tab to my working book? I may be able to figure that out on my own I surely do appreciate all your support.

[bobbyarnold]

I believe the macros are not working on all the tabs, You helped and sent
me a macro in a practice book i tweaked them and applied them to my working copy and if I got any number besides "0" then i thought it worked, but apparently I was mistaken. It appears as though the macros work to some extent then they stop going down to next row. Maybe a break in data some of my tabs have the rows that have no data but a concatenate function and nothing. Do the macros know when they are at the bottom of the rows with data. Some tabs from previous post are not detection any data at all? Idunno! Thanks for all your help