Home |
Search |
Today's Posts |
#1
|
|||
|
|||
array formulas
I have a rather large spreadsheet listing salesmen,
customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... |
#2
|
|||
|
|||
Hi!
You don't necessarily need array formulas but it all depends on what your layout is and what type of data you want to compile. You may be able to use simple lookups or, a lot of people might recommend a pivot table. In any event, you would need to give more details for a more specific suggestion. Biff -----Original Message----- I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... . |
#3
|
|||
|
|||
Perhaps this previous post might give you s
ome possibilities to explore further: http://tinyurl.com/5ejr2 (the key column in your case would be the salesmen names) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... |
#4
|
|||
|
|||
John,
I think a bit more information would help, but here is a starter Assuming the salesmen are list in A1:A100 of sheet1 on Sheet2, add this to A1, assuming a salseman of Bob =IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1COUNTIF(Sheet1!$A$1:$A$100,"Bob "),"",S MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1!$A$1:$ A$100),""),ROW(Sheet1!A1)- ROW(Sheet1!$A$1)+1)) and copy down to A100 This is an array formula so commit with Ctrl-Shift-Enter. You now have the row numbers of the matching salesmen on sheet1 with no gaps. You then just get the data like so in B1 =INDEX(Sheet1!B1:B100,A1) etc. -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... |
#5
|
|||
|
|||
yes, this works great...thanks again for all your help!!
-----Original Message----- John, I think a bit more information would help, but here is a starter Assuming the salesmen are list in A1:A100 of sheet1 on Sheet2, add this to A1, assuming a salseman of Bob =IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1COUNTIF(Sheet1! $A$1:$A$100,"Bob"),"",S MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1! $A$1:$A$100),""),ROW(Sheet1!A1)- ROW(Sheet1!$A$1)+1)) and copy down to A100 This is an array formula so commit with Ctrl-Shift-Enter. You now have the row numbers of the matching salesmen on sheet1 with no gaps. You then just get the data like so in B1 =INDEX(Sheet1!B1:B100,A1) etc. -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... . |
#6
|
|||
|
|||
Just a couple of points, this formula
=INDEX(Sheet1!B1:B100,A1) should be abso9lute for copying down =INDEX(Sheet1!$B$1:$B$100,A1) and you get extra data in the same sort of way =INDEX(Sheet1!$D$1:$D$100,A1) -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... yes, this works great...thanks again for all your help!! -----Original Message----- John, I think a bit more information would help, but here is a starter Assuming the salesmen are list in A1:A100 of sheet1 on Sheet2, add this to A1, assuming a salseman of Bob =IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1COUNTIF(Sheet1! $A$1:$A$100,"Bob"),"",S MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1! $A$1:$A$100),""),ROW(Sheet1!A1)- ROW(Sheet1!$A$1)+1)) and copy down to A100 This is an array formula so commit with Ctrl-Shift-Enter. You now have the row numbers of the matching salesmen on sheet1 with no gaps. You then just get the data like so in B1 =INDEX(Sheet1!B1:B100,A1) etc. -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... . |
#7
|
|||
|
|||
Hi Max,
I wish I had seen that before I re-invented the wheel :-) Regards Bob "Max" wrote in message ... Perhaps this previous post might give you s ome possibilities to explore further: http://tinyurl.com/5ejr2 (the key column in your case would be the salesmen names) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... |
#8
|
|||
|
|||
Bob,
Is it possible to search for all salesmen that are not Bob, Jim or Dave??? (thanks) -----Original Message----- Just a couple of points, this formula =INDEX(Sheet1!B1:B100,A1) should be abso9lute for copying down =INDEX(Sheet1!$B$1:$B$100,A1) and you get extra data in the same sort of way =INDEX(Sheet1!$D$1:$D$100,A1) -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... yes, this works great...thanks again for all your help!! -----Original Message----- John, I think a bit more information would help, but here is a starter Assuming the salesmen are list in A1:A100 of sheet1 on Sheet2, add this to A1, assuming a salseman of Bob =IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1COUNTIF(Sheet1! $A$1:$A$100,"Bob"),"",S MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1! $A$1:$A$100),""),ROW(Sheet1!A1)- ROW(Sheet1!$A$1)+1)) and copy down to A100 This is an array formula so commit with Ctrl-Shift-Enter. You now have the row numbers of the matching salesmen on sheet1 with no gaps. You then just get the data like so in B1 =INDEX(Sheet1!B1:B100,A1) etc. -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... . . |
#9
|
|||
|
|||
Bit clumsy, but can do it for Bob Jim or Dave. Thus to get the row numbers
=IF(ROW(Sheet1!A2)-ROW(Sheet1!$A$2)+1(COUNTIF(Sheet1!$A$2:$A$102,"Bo b")+COU NTIF(Sheet1!$A$2:$A$102,"Jim")+COUNTIF(Sheet1!$A$2 :$A$102,"Dave")),"",SMALL( IF(Sheet1!$A$2:$A$102={"Bob","Jim","Dave"},ROW(She et1!$A$2:$A$102),""),ROW(S heet1!A2)-ROW(Sheet1!$A$2)+1)) I'll work on the exclusive and better formula -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... Bob, Is it possible to search for all salesmen that are not Bob, Jim or Dave??? (thanks) -----Original Message----- Just a couple of points, this formula =INDEX(Sheet1!B1:B100,A1) should be abso9lute for copying down =INDEX(Sheet1!$B$1:$B$100,A1) and you get extra data in the same sort of way =INDEX(Sheet1!$D$1:$D$100,A1) -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... yes, this works great...thanks again for all your help!! -----Original Message----- John, I think a bit more information would help, but here is a starter Assuming the salesmen are list in A1:A100 of sheet1 on Sheet2, add this to A1, assuming a salseman of Bob =IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1COUNTIF(Sheet1! $A$1:$A$100,"Bob"),"",S MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1! $A$1:$A$100),""),ROW(Sheet1!A1)- ROW(Sheet1!$A$1)+1)) and copy down to A100 This is an array formula so commit with Ctrl-Shift-Enter. You now have the row numbers of the matching salesmen on sheet1 with no gaps. You then just get the data like so in B1 =INDEX(Sheet1!B1:B100,A1) etc. -- HTH RP (remove nothere from the email address if mailing direct) "johnT" wrote in message ... I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. thanks in advance.... . . |
#10
|
|||
|
|||
Regarding the original question and Bob's solution, here's a simpler
formula that is not an array formula. I named the salesmen range "Salesmen", including the header cell if there is one. On Sheet2, leave cell A1 blank, and enter the following formula in cell A2. Then copy it down through A101: =IF(COUNTIF(Salesmen,"Bob")<ROW()-1,"",MATCH("Bob",OFFSET(Salesmen,A1,0),0)+A1) This will also get you the row numbers, then follow Bob's solution starting in B2. Gary |
#11
|
|||
|
|||
I wish I had seen that before I re-invented the wheel :-)
LOL! Thought you should be relaxing a little on a Sunday ? -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#12
|
|||
|
|||
Hate to sound like a sourpuss, because I do agree a non-array is better than
an array if you can get it, but there is a fundamental aspect of mine that yours doesn't cover (as presented). If mine is adapted to a named range like yours, it looks better :-) =IF(ROW(Sheet1!A2)-ROW(Salesmen)+1(COUNTIF(Salesmen,"Bob")),"",SMALL (IF(Sal esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1)) The main point though is that yours works fine if the data starts in row 1. Mine works even if the salesmen data starts in row 199. The printout formula needs to still start the index at row 1, but that apart it is resilient. -- HTH RP (remove nothere from the email address if mailing direct) "GaryDK" wrote in message oups.com... Regarding the original question and Bob's solution, here's a simpler formula that is not an array formula. I named the salesmen range "Salesmen", including the header cell if there is one. On Sheet2, leave cell A1 blank, and enter the following formula in cell A2. Then copy it down through A101: =IF(COUNTIF(Salesmen,"Bob")<ROW()-1,"",MATCH("Bob",OFFSET(Salesmen,A1,0),0)+ A1) This will also get you the row numbers, then follow Bob's solution starting in B2. Gary |
#13
|
|||
|
|||
johnT wrote...
I have a rather large spreadsheet listing salesmen, customers, locations etc. I would like to create separate worksheets for each salesman listing only data related to that salesman...on each worksheet i want to referance the master worksheet, how can i do this without having a large group of blank rows??? I think this may be an application for array formulas but i need some help. Many responses with formulas, but if you want to use the single master worksheet for data entry, so each salesperson's data in their own worksheet would effectively be static data, you'd be better off using a macro to to copy records to each individual's worksheet. This could also provide the useful additional functionality of adding new worksheets for new salespersons appearing in the list and deleting worksheets for salespersons no longer appearing in the list (possibly prompting you to confirm deletion). If the sales data table were named SalesTable with salesperson ID in the first/leftmost column, then something like Sub foo() Dim i As Long, j As Long, k As Long, n As Long Dim id As String, ta As String Dim dr As Range, nr As Range, tr As Range Dim xr As Object, ws As Worksheet Set xr = CreateObject("Scripting.Dictionary") Set dr = ThisWorkbook.Names("SalesTable").RefersToRange k = dr.Columns.Count - 1 n = dr.Rows.Count - 1 ta = ActiveSheet.Range("A3", Cells(3, k)).Address(0, 0) 'H/C Set nr = dr.Offset(1, 0).Resize(n, 1) Set tr = dr.Offset(0, 1).Resize(1, k) Set dr = dr.Offset(1, 1).Resize(n, k) For Each ws In ThisWorkbook.Worksheets If ws.Name < dr.Worksheet.Name Then id = ws.Range("B1").Value 'H/C xr.Add id, ws.Name ws.Range("A4:IV65536").ClearContents 'H/C If Application.WorksheetFunction.CountIf(nr, id) = 0 Then If MsgBox( _ Prompt:="Salesperson '" & id & "' has a " & _ "worksheet but no entries in SalesTable." & _ Chr(13) & Chr(13) & "Delete the worksheet?", _ Buttons:=vbYesNo, _ Title:="No Data" _ ) = vbYes Then ws.Delete End If End If End If Next ws For i = 1 To n id = nr.Cells(i, 1).Value If xr.Exists(id) Then j = .Worksheets(xr.Item(id)).Range(ta).End(xlDown).Row If j = Rows.Count Then j = 1 Else j = j - 2 ThisWorkbook.Worksheets( _ xr.Item(id)).Range(ta).Offset(j, 0).Value = _ dr.Rows(i).Value Else Set ws = ThisWorkbook.Worksheets.Add(After:= _ ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt)) xr.Add id, ws.Name ws.Range("A1").Value = _ dr.Offset(-1, -1).Resize(1, 1).Value 'H/C ws.Range("B1").Value = id 'H/C ws.Range(ta).Value = tr.Value ThisWorkbook.Worksheets( _ xr.Item(id)).Range(ta).Offset(1, 0).Value = _ dr.Rows(i).Value End If Next i Set xr = Nothing End Sub |
#14
|
|||
|
|||
Bob,
Nothing sour in that! :-) I have to agree, yours does look better! And I agree that "general purpose" (Although I would opt to tackle this in code as Harlan suggests. The formula I posted isn't sensitive to where the data starts, at least based on a couple of quick tests, but it is sensitive to where *it* starts. That's probably what you meant. When I modify it to be more general purpose (regarding its initial cell placement), it gets a bit uglier. Here it is with a starting cell of J7: =IF(COUNTIF(Salesmen,"Bob")<ROW()-ROW($J$7)+1,"",MATCH("Bob",OFFSET(Salesmen,OFFSET( J7,-1,0),0),0)+OFFSET(J7,-1,0)) Anyway, thanks for your help. It's appreciated. Regards, Gary (less two thru five for direct) Bob Phillips wrote: Hate to sound like a sourpuss, because I do agree a non-array is better than an array if you can get it, but there is a fundamental aspect of mine that yours doesn't cover (as presented). If mine is adapted to a named range like yours, it looks better :-) =IF(ROW(Sheet1!A2)-ROW(Salesmen)+1(COUNTIF(Salesmen,"Bob")),"",SMALL (IF(Sal esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1)) The main point though is that yours works fine if the data starts in row 1. Mine works even if the salesmen data starts in row 199. The printout formula needs to still start the index at row 1, but that apart it is resilient. -- HTH RP (remove nothere from the email address if mailing direct) |
#15
|
|||
|
|||
Hi Gary,
"GaryDK" wrote in message ups.com... The formula I posted isn't sensitive to where the data starts, at least based on a couple of quick tests, but it is sensitive to where *it* starts. That's probably what you meant. When I modify it to be more general purpose (regarding its initial cell placement), it gets a bit uglier. Here it is with a starting cell of J7: =IF(COUNTIF(Salesmen,"Bob")<ROW()-ROW($J$7)+1,"",MATCH("Bob",OFFSET(Salesmen ,OFFSET(J7,-1,0),0),0)+OFFSET(J7,-1,0)) Looks more like mine noe :-) =IF(ROW(Sheet1!A2)-ROW(Salesmen)+1(COUNTIF(Salesmen,"Bob")),"",SMALL (IF(Sal esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
build up an array using formulas | Excel Discussion (Misc queries) | |||
Array formulas | Excel Worksheet Functions | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions | |||
sumif and array formulas | Excel Worksheet Functions | |||
Using wild card characters in array formulas | Excel Worksheet Functions |