Extract columns and create separate sheet based on column criteria

[fbacchus]

Hi,
I have a spreadsheet with the following data:

Order Number In production Cutting Machinist Casualty Analyst Status Date
A100100 01/09/2015 03/09/2015 05/09/2015 John M Cutting 02/09/2015
A100100 01/09/2015 02/09/2015 Mary T Casualty 05/09/2015
A100200 01/09/2015 05/09/2015 Jack S Machinist 02/09/2015
A100200 01/09/2015 02/09/2015 05/09/2015 Mary T In Production 01/09/2015
A100200 01/09/2015 John M Query 09/09/2015
A100300 01/09/2015 02/09/2015 03/09/2015 Frank B In production 01/09/2015
A100300 01/09/2015 02/09/2015 02/09/2015 Jack S

I would like to create separate sheets for each individual based on their name (Analyst) and with some of the columns included in their sheet. So, my first result would be:

Order Number In production Cutting Analyst Status Date
A100100 01/09/2015 03/09/2015 John M Cutting 02/09/2015
A100200 01/09/2015 John M Query 09/09/2015

My Second sheet with be like this:

Order Number In production Cutting Analyst Status Date
A100100 01/09/2015 02/09/2015 Mary T Casualty 05/09/2015
A100200 01/09/2015 02/09/2015 Mary T In Production 01/09/2015

 

[jeffreybrown]

Here is some code I saved from way back given by VoG.

Once you execute this code you can pike the column you want to split out by.

Sub SplitOutSheets1()
    Dim LastCol     As Integer
    Dim iCol        As Integer
    Dim ws          As Worksheet
    Dim Master      As String
    Dim Folder      As String
    Dim fName       As String
    Dim LastRow     As Long
    Dim iStart      As Long
    Dim iEnd        As Long
    Dim i           As Long
    Dim R           As Range
    Dim Sh          As Worksheet
    
    On Error Resume Next
    Set R = Application.InputBox("Click in the column to extract by", Type:=8)
    On Error GoTo 0
    
    If R Is Nothing Then Exit Sub
    iCol = R.Column
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Range(.Cells(2, 1), Cells(LastRow, LastCol)).Sort Key1:=Cells(2, iCol), _
                Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
                Orientation:=xlTopToBottom
        iStart = 2
        
        For i = 2 To LastRow
            If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
                iEnd = i
                Sheets.Add After:=Sheets(Sheets.Count)
                Set ws = ActiveSheet
                On Error Resume Next
                ws.Name = .Cells(iStart, iCol).Value
                On Error GoTo 0
                ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
                With ws.Rows(1)
                    .HorizontalAlignment = xlCenter
                    With .Font
                        .ColorIndex = 5
                        .Bold = True
                    End With
                End With
                .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
                iStart = iEnd + 1
            End If
        Next i
    
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

 

[DocAElstein]

@ jeffreybrown
Hi Jeff,
That code you gave is great. I have seen this sort of requirement solved loads of times using Arrays or Special filter Copy Ways.
I have never seen it done this way- that is to say doing a simple sort on the criteria column to get it in order so that you can then get the Row range to copy from looking up to where the criteria changes. That is really neat.

I only had one problem when I ran the code ( from either a Sheet Code Module or Normal Code Module.) .

The problem was that this line always errors

ws.Range(.Cells(1, 1), .Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value

_...........................

With This mod it was was OK

ws.Range(ws.Cells(1, 1), ws.Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value 'the RHS of = goes by the Application Active Sheet given by the With ActiveSheet at the top of the code. The LHS of = goes to our defined ws

_ That makes sense I suppose: Without that extra ws qualifier, the unqualified .Cells call would go to
Either:
_ the Application Active Sheet in the case of in a Normal Module, - so is still going by the
With ActiveSheet
from the top of the code, which referenced the main sheet.
Or
_ in the case of the Sheet Module it will go to the Sheet i happened to be in ( which of course will never that of the sheet I just made!! ).

Thanks for adding that solution alternative!
Alan

 

[DocAElstein]

Hi,
I have a spreadsheet with the following data:.......

@ fbacchus
Hi fbacchus, I started before jeff did his code, so i finished. ( Jeff’s way BTW seems amazing!! )

I Guessed the Forum editor had “eaten” spaces you may have originally put in to show in which column the data should be?

It is much better if you can learn to use the Forum Tools to post us a screenshot that we can copy to a Spreadsheet
http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

Failing that just try to give us that information in a Table (Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you need to click on the “Go Advanced” Button next to the Reply Button) )

_..............................................................

Anyway, I have a code that is flexible and ‘commented well enough that you should be able to change it a bit, as i have almost certainly not guessed you initial Table correct.

This is the table i will work on in the first sheet tab looking from the left ( Usually Sheet 1)

Using Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Order Number	In Production	Cutting	production	Machinist	Casualty	Analyst	Status	Date
2​	A100100	01.09.2015​	03.09.2015​	05.09.2015​			John M	Cutting	02.09.2015​
3​	A100100	01.09.2015​				02.09.2015​	Mary T	Casualty	05.09.2015​
4​	A100200	01.09.2015​			05.09.2015​		Jack S	Machinist	02.09.2015​
5​	A100200	01.09.2015​	02.09.2015​	05.09.2015​			Mary T	In Production	01.09.2015​
6​	A100200	01.09.2015​					John M	Query	09.09.2015​
7​	A100300	01.09.2015​	02.09.2015​	03.09.2015​			Frank B	In production	01.09.2015​
8​	A100300	01.09.2015​	02.09.2015​	02.09.2015​			Jack S

Tabelle1

_..................................................

This is basically what the code i give you is doing.

Rem 1 ) Sets up References to get at main sheet info easily. ( vLkUpc is the column Number where your names are or should be )

Rem 2) This is one of many ways to get a unique listing of Names.

Rem 3) The main loop, looping based on the Unique names gets the rows to be copied, puts them in an Array, and then makes a sheet and pastes that Array info info in.

Note:
_(i)_ ‘3c) assumes you want all columns in your output )
( Alternatively you will see in the Code a hard code line to allow you to pick out the columns you require )

_(ii)_ There is a Function ( from shg ) that you require also to get the last Column Letter if you want all the columns. Just copy both the main Sub Routine and the Function to the same Code Module.


_.....................................................

If you run the code I give on the Test data from the above Table then 4 new Worksheets are produced as follows

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Order Number	In Production	Cutting	production	Machinist	Casualty	Analyst	Status	Date
2​	A100100	01.09.2015	03.09.2015	05.09.2015			John M	Cutting	02.09.2015
3​	A100200	01.09.2015					John M	Query	09.09.2015

John M

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Order Number	In Production	Cutting	production	Machinist	Casualty	Analyst	Status	Date
2​	A100100	01.09.2015				02.09.2015	Mary T	Casualty	05.09.2015
3​	A100200	01.09.2015	02.09.2015	05.09.2015			Mary T	In Production	01.09.2015

Mary T

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Order Number	In Production	Cutting	production	Machinist	Casualty	Analyst	Status	Date
2​	A100200	01.09.2015			05.09.2015		Jack Sht	Machinist	02.09.2015
3​	A100300	01.09.2015	02.09.2015	02.09.2015			Jack Sht

Jack Sht

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Order Number	In Production	Cutting	production	Machinist	Casualty	Analyst	Status	Date
2​	A100300	01.09.2015	02.09.2015	03.09.2015			Frank B	In production	01.09.2015

Frank B

_...........................................................

Good luck!
Alan

_....................................................

Here is the code: ( It is a bit easier to see in the VB Editor Window as the ‘Green Comments go off to the right out of the way!! ) ( Do not forget to copy and include the Function as well.


Option Explicit

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] fbacchusMasterToShts() [color=darkgreen]'http://www.mrexcel.com/forum/excel-questions/926943-extract-columns-create-separate-sheet-based-column-criteria.html[/color]
Rem 1) Master Worksheets info.
[color=blue]Dim[/color] wsM [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wsM = ThisWorkbook.Worksheets.Item(1) [color=darkgreen]'Initial Pointer to Memory for Stuff refering to First Worksheet Object from left. Allows use of intellisense to give selection of all Properties and Methods we can get afor the wsM Object[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], lc [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Variable for last table Row and Column. Varaibe of fixed memory size for a Long number. ( [color=blue]Long[/color] is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) -Most smaller numbers, Byte, Integer, Single are converted in computer to long so no advantage of Dim to smaller Type here)[/color]
[color=blue]Let[/color] lr = wsM.Cells.Find(What:="*", After:=wsM.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [color=darkgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers). Finds last row in sheet rather than row for last entry in particular cell[/color]
[color=blue]Let[/color] lc = wsM.Cells(1, Columns.Count).End(xlToLeft).Column [color=darkgreen]'Last column with entry in heading. Found by starting at last cell Range Object ( last column = Columns.Count ) in row 1, then using .End Property to "look backwards" (ToLeft) until something is found and returning a New Range Object of last cell with something in it, then applying ot that new range Object the column Property to get the column number.[/color]

[color=blue]Dim[/color] rw [color=blue]As[/color] Long [color=darkgreen]'Will be used genearally for referrencig "row" or "vertical" whole Numbers[/color]
[color=blue]Dim[/color] vLkUpc [color=blue]As[/color] [color=blue]Long[/color]: [color=blue]Let[/color] vLkUpc = 7 [color=darkgreen]'Column where names are[/color]
[color=blue]Dim[/color] rngIn [color=blue]As[/color] Range: [color=blue]Set[/color] rngIn = wsM.Range("A1", wsM.Cells(lr, lc)) [color=darkgreen]'rgnIn gives set to pointer to where Range Object of out inpuut Table is[/color]
[color=darkgreen]'1a) Optional Start Bit to  Delete Sheets / Tabs------------[/color]
Application.DisplayAlerts = [color=blue]False[/color] [color=darkgreen]'Prevents being asked everytime if you really want to delete the Workbook[/color]
    [color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet [color=darkgreen]'Tempory worksheet name. ws now has Methods and Properties of Worksheets obtained with typing . dot after it![/color]
    [color=blue]For[/color] [color=blue]Each[/color] ws [color=blue]In[/color] ActiveWorkbook.Worksheets [color=darkgreen]'We could alternatively use something like ThisWorkbook, Workbooks("Itsname.xlxm")[/color]
        [color=blue]If[/color] ws.Name <> "ASheetToKeep" And ws.Name <> wsM.Name And ws.Name <> "Sheet1" And ws.Name <> "Tabelle1" [color=blue]Then[/color] [color=darkgreen]'Put WWorksheets here you never want to delete[/color]
        ws.Delete
        [color=blue]Else[/color] [color=darkgreen]'Presumably then the worksheet name is That of the first sheet so[/color]
        [color=darkgreen]' do nothing (Don't delete it!)[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] ws
Application.DisplayAlerts = [color=blue]True[/color] [color=darkgreen]'Turn it back on[/color]
[color=darkgreen]'End Bit to delete any Sheets / Tabs------------[/color]
Rem 2) Unique Names. Use the "resize an Array and put value in if it is not already there" way, as it is fast.
[color=blue]Dim[/color] vLkUp() [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Dynamic 2 D 1 "column" array for LookUpColumn The .Index Method or .Value Property used later retuns an Field of Variant Type Elements[/color]
[color=blue]Dim[/color] rngvLkUp [color=blue]As[/color] Range
[color=darkgreen]'    Set rngvLkUp = Application.WorksheetFunction.Index(rngIn, 0, vLkUpc) 'Index Function with second argument ("row" co - ordinate) set to 0 will return the entire "column" given by third argument ( "column" - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number)  http://www.excelforum.com/excel-new-users-basics/1080634-vba-1-dimensional-horizontal-and-vertical-array-conventions-ha-1-2-3-4-a.html   https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/[/color]
[color=darkgreen]'    Let vLkUp() = rngvLkUp.Value 'Returns format type (1,1) (2,1) (3,1) (4,1)...[/color]
[color=darkgreen]'    Let vLkUp() = Application.WorksheetFunction.Index(rngIn, 0, vLkUpc).Value[/color]
[color=blue]Let[/color] vLkUp() = wsM.Range(wsM.Cells(1, vLkUpc), wsM.Cells(lr, vLkUpc)).Value [color=darkgreen]' .Value Property applied to Range greater than 1 cell Returns Field of Variant Type Element Values that may be assigned directly to a dynamic Array of variant types[/color]
    [color=blue]Dim[/color] Eunuch() [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'Array used for unique LookUpColumn values to be used as both Workbook Names and Search Criteria[/color]
    [color=blue]ReDim[/color] Eunuch(1 [color=blue]To[/color] 1) [color=darkgreen]'We need to size it initially, but it must be a dynamic Array to allow us to increase it's size, hence not done by Dim(1 to 1) which would make it a non dynamic Array[/color]
        [color=blue]For[/color] rw = 1 [color=blue]To[/color] [color=blue]UBound[/color](vLkUp(), 1) [color=blue]Step[/color] 1 [color=darkgreen]'for each "row" in the Look Up Column ( we want to look at the values there )[/color]
            [color=blue]If[/color] IsError(Application.Match(vLkUp(rw, 1), Eunuch(), 0)) [color=blue]Then[/color] [color=darkgreen]'Match would error if the Unique value was not in the Array for those unique values, in which case ...[/color]
            [color=blue]ReDim[/color] [color=blue]Preserve[/color] Eunuch(1 [color=blue]To[/color] [color=blue]UBound[/color](Eunuch()) + 1): [color=blue]Let[/color] Eunuch(UBound(Eunuch())) = vLkUp(rw, 1) [color=darkgreen]'.. if not there then we increase the size of the Uniques Array, then put it in, ( Note the first Array Element is empty, currently and not used***[/color]
            Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]'case a unique value already there for this "row". So we do nothing[/color]
        [color=blue]Next[/color] rw
    [color=blue]Let[/color] Eunuch(1) = "Uniques" [color=darkgreen]'***We do not use the first element of the Array, so just for fun put a Title in it. No special Reason for this[/color]

Rem 3) Main [color=blue]Loop[/color]==============================================================
[color=blue]Dim[/color] Cnt [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Count for our required Sheets[/color]
    [color=blue]For[/color] Cnt = 3 [color=blue]To[/color] [color=blue]UBound[/color](Eunuch()) [color=darkgreen]'Doing for all of the Names whiich start at 3rd Element of our Eunuchs Array[/color]
    [color=darkgreen]'3b)An Array of our required row indicies is needed in a further code line[/color]
    [color=blue]Dim[/color] rws() [color=blue]As[/color] [color=blue]String[/color], rwsT() [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Arrays for these indicies, dynamic as we do not know the size. rws() uses a Strings Object Function returning an Array of strings. rwsT we fill so can choose the type[/color]
    [color=blue]Dim[/color] strRws [color=blue]As[/color] [color=blue]String[/color], Indicie [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'A string to hold found required indicies and a Loop Bound Variable Count for for looping thriug them[/color]
    [color=blue]Let[/color] strRws = "1" [color=darkgreen]'Assumne you allways want the heading row[/color]
        [color=blue]For[/color] Indicie = 2 [color=blue]To[/color] lr [color=darkgreen]'For all but heading row[/color]
            [color=blue]If[/color] vLkUp(Indicie, 1) = Eunuch(Cnt) [color=blue]Then[/color] [color=darkgreen]'Condition of name match[/color]
            [color=blue]Let[/color] strRws = strRws & " " & Indicie [color=darkgreen]'Add indicie to Array of them[/color]
            Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]'No name match so do not add indicie to Array of them[/color]
        [color=blue]Next[/color] Indicie
    [color=blue]Let[/color] rws() = Split("" & strRws & "", " ") [color=darkgreen]'This returns a 1 d "pseudo horizontal" Array of ou indicies ( starting at base 0 )[/color]
    [color=blue]ReDim[/color] rwsT(1 [color=blue]To[/color] (UBound(rws()) + 1), 1 [color=blue]To[/color] 1) [color=darkgreen]'We need this "orientation" Array later. ( +1 as rws() starts at 0 )[/color]
        [color=blue]For[/color] rw = 1 [color=blue]To[/color] (UBound(rws()) + 1) [color=darkgreen]' this is to transpose to a 2 D 1 "column" Array which we need. Do it in a loop as .Transpose is crap      http://excelmatters.com/2016/03/08/transpose-bug-in-2013-and-2016/[/color]
        [color=blue]Let[/color] rwsT(rw, 1) = [color=blue]CLng[/color](rws(rw - 1)) [color=darkgreen]'Put appropriate value in ( -1 as rws() starts at 0 ).[/color]
        [color=blue]Next[/color] rw
    [color=darkgreen]'3c)An Array of required column indicies[/color]
    [color=blue]Dim[/color] clms() [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Dynamic Array as values are obtained from VBA Evaluate Function that returns a Field Of Variant Type Elements[/color]
    [color=blue]Dim[/color] strlc [color=blue]As[/color] String: [color=blue]Let[/color] strlc = FucshgMathsVBA(lc) [color=darkgreen]'Use Function from shg for last column letter from last column number[/color]
    [color=blue]Let[/color] clms() = Evaluate("=" & "column(A:" & strlc & ")") [color=darkgreen]'Spreadsheet Column Function used for convenience when all column indicies are required[/color]
    [color=blue]Let[/color] clms() = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) [color=darkgreen]'Alternaive if you wish to select a specific columns ( and / or order )[/color]
    [color=darkgreen]'3d)Make an Array based on our require rows and columns[/color]
    [color=blue]Dim[/color] arrOut() [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Array type and Element Type to suit returned Element Field from following line.[/color]
    [color=darkgreen]'Let arrOut() = Application.Index(Cells, rwsT(), clms()) '  Still bit of a mystery here    http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html    http://www.eileenslounge.com/viewtopic.php?f=4&t=22534#p175261[/color]
    [color=blue]Let[/color] arrOut() = Application.Index(rngIn.Value, rwsT(), clms()) [color=darkgreen]'  This is a bit slower, but seems necerssary to get Date Format Correct.[/color]
    [color=darkgreen]'3e) create new Worksheet if necerssary[/color]
    [color=blue]Dim[/color] wsCnt [color=blue]As[/color] Worksheet [color=darkgreen]'Temporary variable for new Worksheet[/color]
        [color=blue]If[/color] [color=blue]Not[/color] Evaluate("=ISREF('" & Eunuch(Cnt) & "'" & "!A1)") Then [color=darkgreen]'Check to see if the sheet is there by seeing if the reference to cell A1 in that sheet doesn't exist. If it is true that it does not exist, then[/color]
        Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "" & Eunuch(Cnt) & "" [color=darkgreen]'Make it as that after the last sheet[/color]
        [color=blue]Else[/color]
        Sheets("" & Eunuch(Cnt) & "").Move After:=Worksheets(Worksheets.Count) [color=darkgreen]'Otherwise If the sheet is there it could be anywhere so we put it after last sheet[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Set[/color] wsCnt = ActiveSheet [color=darkgreen]'Nit necerssary but Good practice to do this after sheet is made and so is "there"[/color]
    [color=darkgreen]'3f) Paste in required Final range[/color]
    wsCnt.Range("A1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut() [color=darkgreen]''A nice "One" liner - Use Resize Property on Top left cell Range Object of where outpit should go to resize to size of output Array and then the allowed VBA assignment of a collection of Values to a Spreadsheet Range ot an Array is used[/color]
    [color=blue]Next[/color] Cnt [color=darkgreen]'Go to next Unique Name[/color]
[color=darkgreen]'End main loop================================================================[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

[color=darkgreen]'   http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html[/color]
[color=blue]Function[/color] FucshgMathsVBA([color=blue]ByVal[/color] lclm [color=blue]As[/color] Long) As [color=blue]String[/color] [color=darkgreen]'[/color]
        '    Dim vtemp
        [color=darkgreen]'    Let vtemp = IIf(((((lclm - 1) \ 26) - 1) \ 26) <> 0, Chr(65 + (((((lclm - 1) \ 26) - 1) \ 26) - 1 Mod 26)), "")[/color]
        [color=darkgreen]'    Let vtemp = Evaluate("IF(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26), CHAR(MOD(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26), 26)-1 + 65), """") ")[/color]
        [color=darkgreen]'    Let vtemp = IIf(((lclm - 1) \ 26) <> 0, Chr(65 + (((lclm - 1) \ 26) - 1) Mod 26), "")[/color]
        [color=darkgreen]'    Let vtemp = Evaluate("IF(QUOTIENT(" & lclm & "-1, 26), CHAR(MOD(QUOTIENT(" & lclm & "-1, 26)-1, 26) + 65), """")")[/color]
        [color=darkgreen]'    Let vtemp = IIf(lclm <> 0, Chr(65 + (lclm - 1) Mod 26), "")[/color]
        [color=darkgreen]'    Let vtemp = Evaluate("IF(" & lclm & ", CHAR(MOD(" & lclm & "-1, 26) + 65), """") & """"")[/color]
        [color=darkgreen]'    Let FucshgMathsVBA = IIf(((((lclm - 1) \ 26) - 1) \ 26) <> 0, Chr(65 + (((((lclm - 1) \ 26) - 1) \ 26) - 1 Mod 26)), "") & IIf(((lclm - 1) \ 26) <> 0, Chr(65 + (((lclm - 1) \ 26) - 1) Mod 26), "") & IIf(lclm <> 0, Chr(65 + (lclm - 1) Mod 26), "")[/color]
[color=blue]Let[/color] FucshgMathsVBA = IIf(((((lclm - 1) \ 26) - 1) \ 26), Chr(65 + (((((lclm - 1) \ 26) - 1) \ 26) - 1 Mod 26)), "") & IIf(((lclm - 1) \ 26), Chr(65 + (((lclm - 1) \ 26) - 1) Mod 26), "") & IIf(lclm, Chr(65 + (lclm - 1) Mod 26), "")
        [color=darkgreen]'    Let FucshgMathsVBA = Evaluate("IF(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26), CHAR(MOD(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26)-1, 26) + 65), """") & IF(QUOTIENT(" & lclm & "-1, 26), CHAR(MOD(QUOTIENT(" & lclm & "-1, 26)-1, 26) + 65), """") & IF(" & lclm & ", CHAR(MOD(" & lclm & "-1, 26) + 65), """") & """"")[/color]
[color=blue]End[/color] [color=blue]Function[/color]

 

[fbacchus]

Thanks Alan. I'll work it thru as you suggested.

frank

 

[DocAElstein]

Hi franc

Thanks Alan. I'll work it thru as you suggested.
frank

OK
Note: to get my code to work the same as Jeff's ( note the corrections to his code I mentioned ), then you will just need to change the 7 here
vLkUpc = 7
to the actual column number where your People's names are.
But worth having a work through, ( and check the jem on Forum Posting, Tables, Screen shots etc...
If you need more help I will try to catch you tomorrow
Alan

 

[fbacchus]

Allan:

Thanks again. This is great. I would like to expand on it a little further:
(1) I would like to have selected fields in the output spreadsheets : Order Number, Production, Casualty, Analyst & Status
(2) I would like to select only those rows with Analyst being Mary T and Status is "In Production"
(3) I would like to add a data and time stamp on the last row of each sheet.

Is this possible? Thanks.

frank

 

[DocAElstein]

......
Is this possible? Thanks.

frank

It very likely is, - there 'aint much of that nature that can not be done with VBA.
For example as regards your ( 1 ). If you go through and read the ' comments in my code you will see immediately how my code allows you to do that with a very simple mod. This is because my code selects ( currently set to all ) the columns you actually want. ( This would not be as easy with Jeff's code as this code simply copies the entire row of the Table ).

I will take a look at the rest tomorrow

In the meantime check first that the codes we gave are doing what you originally wanted and feed that info back to us.
( And then try out doing ( 1 ) yourself, and let us know how you get on )

And try to give some clear Tables / Screen Shots of what exactly you now want based on the given Test data. ( Or different data if required to test out all the possible scenarios you can invisage )

 

[fbacchus]

Hi DocAElstein :

Thanks again. The original code you sent back fulfilled the original request I requested. I tried what you suggested for accomplish No 1. and that works too. I appreciate it. No 2 is a selection of records meeting specific conditions and No 3 is a display of date and time-stamp when the spreadsheet was created.

Order Number In Production Analyst Status
A100200 1/12/2015 Mary T In Production Run on: 03/13/2016 09:03 pm

I apologize, but I am not familiar with using the tool to insert a screen shot. Where can I find the information on that? I used the same data from above and selected only those rows where Analyst equal Mary T and Status equal "In Production". Then I added the data time-stamp at the end of the last record.

Hope this helps and that for your help.

frank

 

[DocAElstein]

Hi Frank
Regarding the Tools, it is a bit of effort initially to get them up and running but well worth taking the effort. You will greatly improve your chance of getting help if you can use them to clearly show what you have and what you want.
All the info you need is in my signature below, and at the start of my Post # 4 in this Thread . Please take a look through everything. There is basically a HTML Code Generator Add in and a BB Code generator Add in
Once Again this will give you all the info for the HTML Code generator
Add – In https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813
Notes - http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970
This for the BB Code Generator.
Add – In https://www.dropbox.com/s/31r9s6t9j69lkab/Forum Tools.xlam?dl=0
Notes ( Mine a bit rough ) https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv

Alternatively I also suggested in Post # 4 the Table icon option.
What ever you chose, try them out here in the test area:
Test Here

_..............................................................................

From your
A100200 1/12/2015 Mary T In Production Run on: 03/13/2016 09:03 pm
It is very unclear how that should look on the final Spreadsheet. I see for example in your original data no date of 1/12/2015. Your requirements sound fairly easy, but the way you are presenting your test data is very confusing. Remember you know exactly what it looks like on your Spreadsheet. I do not.
And remember The point again that the Forum editor “eats” spaces greater than 1, so it is impossible to see from the Info you give which and where columns are, hence the table producing alternatives I suggested.

It might help if you post the code you have working, then at least I can reproduce what you are actually looking at. Remember to use Code tags for posting us code. See again my signature, and again practice please in the Test Area.

Alan