|
Uniqe list
I have a large problem that I cannot find a simple formula for. Been looking
and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Check out Advanced Filter in Help, including the Unique Values only
option. For more, see http://contextures.com/xladvfilter01.html In article , Jason@Simcon wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you |
Uniqe list
A query would probably address your problem, but I am not sure how to do this
in Excel. An Access database would bot be that hard to setup if your of a mind to go that route. You have a great deal of flexibility to screen data in that environment and the performance is normally quite good unless your working with a huge number of records or executing an extremely complex query with a lot of conditions and calculations occuring. Just a thought. I belive you can execute queries in Excel through VBA, but I have not tried that yet. One more thing, you can use Access to collect the data and then export to Excel to do things like graphing. "Jason@Simcon" wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Hi
use 'Data - Filter - Advanced Filter'< and check 'Unique entries'. or use the following addin: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
This macro will copy your range to another sheet, sort it,
then delete all the extra entires leaving you with a unique list. it is case sensative. Sub Macro1() Sheets("Sheet1").Select Range("A1:A100").Copy Sheets.Add ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Dim tc As Range Dim ntc As Range Set tc = Range("a1") Do While Not IsEmpty(tc) Set ntc = tc.Offset(1, 0) If tc.Value = ntc.Value Then tc.EntireRow.Delete Set tc = ntc Else Set tc = ntc End If Loop End Sub make changes to the sheet names and range references. good luck. -----Original Message----- I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason . |
Uniqe list
A query wouldnt work because automatically filtering this info is only one
step of many that needs to be automatic. Simply put, We are dumping loads of data into a storage doc, then want to create automatic reports. I know...........sounds like access, but I find access very limited in the look of the final report. Also, I am looking to have 100% of the report to be automatic. I do it all the time but with a price in computer performance. This new formula, (if I fiind it) may get me around this problem. "Kevin" wrote: A query would probably address your problem, but I am not sure how to do this in Excel. An Access database would bot be that hard to setup if your of a mind to go that route. You have a great deal of flexibility to screen data in that environment and the performance is normally quite good unless your working with a huge number of records or executing an extremely complex query with a lot of conditions and calculations occuring. Just a thought. I belive you can execute queries in Excel through VBA, but I have not tried that yet. One more thing, you can use Access to collect the data and then export to Excel to do things like graphing. "Jason@Simcon" wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Am looking for the unique list to be automatic with no action on the users
part. This is only one step of many on this type of doc. "JE McGimpsey" wrote: Check out Advanced Filter in Help, including the Unique Values only option. For more, see http://contextures.com/xladvfilter01.html In article , Jason@Simcon wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you |
Uniqe list
Hi Frank
Still not the answer. Please see my answers to the other posts. Thanks to all for their input and possible continuing input until I get it solved. Will let alll know if I find it. "Frank Kabel" wrote: Hi use 'Data - Filter - Advanced Filter'< and check 'Unique entries'. or use the following addin: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Hi,
There has got to be an easier answer. If you look at it this is a very simple problem. I see people asking here and on newsgroups this type of question all the time and all the answers are like this one. You need a phd to deal with that answer. (or maybe it is just me :) ) Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an impressivley simple problem to solve? Excel needs a formula for just this problem. Just like vlookup or sumif, ETC.... Something like =NOBLANKSNOREPEATS(A1:A50) as an array formula. Do my answers sound frustrated? Trying not to be. Been working on this along time. Thanks to everyone for continuing to try to help. " wrote: This macro will copy your range to another sheet, sort it, then delete all the extra entires leaving you with a unique list. it is case sensative. Sub Macro1() Sheets("Sheet1").Select Range("A1:A100").Copy Sheets.Add ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Dim tc As Range Dim ntc As Range Set tc = Range("a1") Do While Not IsEmpty(tc) Set ntc = tc.Offset(1, 0) If tc.Value = ntc.Value Then tc.EntireRow.Delete Set tc = ntc Else Set tc = ntc End If Loop End Sub make changes to the sheet names and range references. good luck. -----Original Message----- I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason . |
Uniqe list
Jason@Simcon wrote:
Hi Frank Still not the answer. Please see my answers to the other posts. Thanks to all for their input and possible continuing input until I get it solved. Will let alll know if I find it. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might use MyArray = ArrayUniques("Range("A:A")) This will be case sensitive, will include one blank if the range contains one or more blanks, and will be a 1-based vertical array. If you want to omit the blank, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True) And if you also don't want it case sensitive, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True, MatchCase:=False) There is also a Base_Orient parameter that can be set to "1horiz", "0horiz" or "0vert" as desired. Any of the above can be followed by Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Alan Beban I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Hi
have you tried using a pivot table for this? -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... A query wouldnt work because automatically filtering this info is only one step of many that needs to be automatic. Simply put, We are dumping loads of data into a storage doc, then want to create automatic reports. I know...........sounds like access, but I find access very limited in the look of the final report. Also, I am looking to have 100% of the report to be automatic. I do it all the time but with a price in computer performance. This new formula, (if I fiind it) may get me around this problem. "Kevin" wrote: A query would probably address your problem, but I am not sure how to do this in Excel. An Access database would bot be that hard to setup if your of a mind to go that route. You have a great deal of flexibility to screen data in that environment and the performance is normally quite good unless your working with a huge number of records or executing an extremely complex query with a lot of conditions and calculations occuring. Just a thought. I belive you can execute queries in Excel through VBA, but I have not tried that yet. One more thing, you can use Access to collect the data and then export to Excel to do things like graphing. "Jason@Simcon" wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
No. I am not familiar with that. I will look into it to see if that is what
we are looking for. Thank you. "Frank Kabel" wrote: Hi have you tried using a pivot table for this? -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... A query wouldnt work because automatically filtering this info is only one step of many that needs to be automatic. Simply put, We are dumping loads of data into a storage doc, then want to create automatic reports. I know...........sounds like access, but I find access very limited in the look of the final report. Also, I am looking to have 100% of the report to be automatic. I do it all the time but with a price in computer performance. This new formula, (if I fiind it) may get me around this problem. "Kevin" wrote: A query would probably address your problem, but I am not sure how to do this in Excel. An Access database would bot be that hard to setup if your of a mind to go that route. You have a great deal of flexibility to screen data in that environment and the performance is normally quite good unless your working with a huge number of records or executing an extremely complex query with a lot of conditions and calculations occuring. Just a thought. I belive you can execute queries in Excel through VBA, but I have not tried that yet. One more thing, you can use Access to collect the data and then export to Excel to do things like graphing. "Jason@Simcon" wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Thankyou Alan.
I will try that and let everyone know how it works. "Alan Beban" wrote: Jason@Simcon wrote: Hi Frank Still not the answer. Please see my answers to the other posts. Thanks to all for their input and possible continuing input until I get it solved. Will let alll know if I find it. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might use MyArray = ArrayUniques("Range("A:A")) This will be case sensitive, will include one blank if the range contains one or more blanks, and will be a 1-based vertical array. If you want to omit the blank, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True) And if you also don't want it case sensitive, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True, MatchCase:=False) There is also a Base_Orient parameter that can be set to "1horiz", "0horiz" or "0vert" as desired. Any of the above can be followed by Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Alan Beban I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Hi
some starting points: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...simple-pivot-t able/trackback/ -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... No. I am not familiar with that. I will look into it to see if that is what we are looking for. Thank you. "Frank Kabel" wrote: Hi have you tried using a pivot table for this? -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... A query wouldnt work because automatically filtering this info is only one step of many that needs to be automatic. Simply put, We are dumping loads of data into a storage doc, then want to create automatic reports. I know...........sounds like access, but I find access very limited in the look of the final report. Also, I am looking to have 100% of the report to be automatic. I do it all the time but with a price in computer performance. This new formula, (if I fiind it) may get me around this problem. "Kevin" wrote: A query would probably address your problem, but I am not sure how to do this in Excel. An Access database would bot be that hard to setup if your of a mind to go that route. You have a great deal of flexibility to screen data in that environment and the performance is normally quite good unless your working with a huge number of records or executing an extremely complex query with a lot of conditions and calculations occuring. Just a thought. I belive you can execute queries in Excel through VBA, but I have not tried that yet. One more thing, you can use Access to collect the data and then export to Excel to do things like graphing. "Jason@Simcon" wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Thank you,
Will look into those sites. I appreciate your help. "Frank Kabel" wrote: Hi some starting points: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...simple-pivot-t able/trackback/ -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... No. I am not familiar with that. I will look into it to see if that is what we are looking for. Thank you. "Frank Kabel" wrote: Hi have you tried using a pivot table for this? -- Regards Frank Kabel Frankfurt, Germany "Jason@Simcon" schrieb im Newsbeitrag ... A query wouldnt work because automatically filtering this info is only one step of many that needs to be automatic. Simply put, We are dumping loads of data into a storage doc, then want to create automatic reports. I know...........sounds like access, but I find access very limited in the look of the final report. Also, I am looking to have 100% of the report to be automatic. I do it all the time but with a price in computer performance. This new formula, (if I fiind it) may get me around this problem. "Kevin" wrote: A query would probably address your problem, but I am not sure how to do this in Excel. An Access database would bot be that hard to setup if your of a mind to go that route. You have a great deal of flexibility to screen data in that environment and the performance is normally quite good unless your working with a huge number of records or executing an extremely complex query with a lot of conditions and calculations occuring. Just a thought. I belive you can execute queries in Excel through VBA, but I have not tried that yet. One more thing, you can use Access to collect the data and then export to Excel to do things like graphing. "Jason@Simcon" wrote: I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Alan,
Using the same example I provided in the initial question, I attempted to use your simple formula in many different ways. Basically I inputed the array formula in B1 like this:{=ARRAYUNIQUES(A1:A10($B$1:B1))} and, as I am sure you know, came up with nothing no matter how I toyed with it. Can you get a little more specific with the formula itself? FYI; I realize you said it is case sensitive but when you hit CTRL+SHFT+ENTR it automatically caps it. Oh, and, your site you sent me to is way over my head. Sorry. "Alan Beban" wrote: Jason@Simcon wrote: Hi Frank Still not the answer. Please see my answers to the other posts. Thanks to all for their input and possible continuing input until I get it solved. Will let alll know if I find it. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might use MyArray = ArrayUniques("Range("A:A")) This will be case sensitive, will include one blank if the range contains one or more blanks, and will be a 1-based vertical array. If you want to omit the blank, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True) And if you also don't want it case sensitive, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True, MatchCase:=False) There is also a Base_Orient parameter that can be set to "1horiz", "0horiz" or "0vert" as desired. Any of the above can be followed by Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Alan Beban I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Jason@Simcon wrote:
Alan,. . .your site you sent me to is way over my head. Sorry. Ah, fear of the unknown. FWIW, Pivot Tables are way over my head :-) Before giving up, try the following: Go back to the site. Click on the Array Functions link in the lower left corner of the page (this will download the file) Save the file as a Microsoft Excel Add-In In your working file go to the VBE (Visual Basic Editor) Click on Tools|References, check the item named Array Functions, then OK You should now be able to get results from coding in your working file as my response indicated; e.g., MyArray = ArrayUniques(Range("A:A")) Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Good luck, Alan Beban "Alan Beban" wrote: Jason@Simcon wrote: Hi Frank Still not the answer. Please see my answers to the other posts. Thanks to all for their input and possible continuing input until I get it solved. Will let alll know if I find it. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might use MyArray = ArrayUniques("Range("A:A")) This will be case sensitive, will include one blank if the range contains one or more blanks, and will be a 1-based vertical array. If you want to omit the blank, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True) And if you also don't want it case sensitive, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True, MatchCase:=False) There is also a Base_Orient parameter that can be set to "1horiz", "0horiz" or "0vert" as desired. Any of the above can be followed by Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Alan Beban I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary It seems so simple and I cant believe Microsoft does not have a formula or function for this. Keep in mind that this is an example and the real worksheets have thousands of entries with 100s of unique pieces of data. This appears to be a fundamental stopping point for me in creating spreadsheets that automatically create reports for me without bogging down the computer. I have gotten around it by using Rank, match, and index but it really bogs me down and if it doesnt go into "not responding" then it could take up to 10 minutes to open. (Pent 4.., etc. state of the art up to date computers) Would very very much appreciate a simple formula that can achieve this. Thank you -- Jason |
Uniqe list
Or, to use it as a formula, after checking the item named Array
Functions, then on the worksheet array enter into B1:B100 =ArrayUniques(A:A) Alan Beban Alan Beban wrote: Jason@Simcon wrote: Alan,. . .your site you sent me to is way over my head. Sorry. Ah, fear of the unknown. FWIW, Pivot Tables are way over my head :-) Before giving up, try the following: Go back to the site. Click on the Array Functions link in the lower left corner of the page (this will download the file) Save the file as a Microsoft Excel Add-In In your working file go to the VBE (Visual Basic Editor) Click on Tools|References, check the item named Array Functions, then OK You should now be able to get results from coding in your working file as my response indicated; e.g., MyArray = ArrayUniques(Range("A:A")) Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Good luck, Alan Beban "Alan Beban" wrote: Jason@Simcon wrote: Hi Frank Still not the answer. Please see my answers to the other posts. Thanks to all for their input and possible continuing input until I get it solved. Will let alll know if I find it. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might use MyArray = ArrayUniques("Range("A:A")) This will be case sensitive, will include one blank if the range contains one or more blanks, and will be a 1-based vertical array. If you want to omit the blank, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True) And if you also don't want it case sensitive, use MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True, MatchCase:=False) There is also a Base_Orient parameter that can be set to "1horiz", "0horiz" or "0vert" as desired. Any of the above can be followed by Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray Alan Beban I have a large problem that I cannot find a simple formula for. Been looking and trying now for over a year. As follows: A1:A100 is filled with data that is often repeated. There may also be some blanks. In this data there may be anywhere from 2 to 100 unique pieces of data that I would like to extract and put in column B1 thru B....(however many unique pieces of data have been extracted). Example: A B 1 George George 2 Sam Mike 3 Mike Sam 4 Sam Zachary 5 George 6 Mike 7 Zachary 8 Sam 9 George 10 Zachary |
Uniqe list
Hi Jason:
"Jason@Simcon" wrote: Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an iimpressively simple problem to solve? obviously not :) John Walkenbach the author of the Bible, no not that Bible, the Excel Bible has written a procedure and made it available for download on his site. http://www.j-walk.com/ss search for "nodupes" without the quotes Good Luck TK |
Uniqe list
|
Uniqe list
TK wrote:
Hi Jason: "Jason@Simcon" wrote: Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an iimpressively simple problem to solve? obviously not :) John Walkenbach the author of the Bible, no not that Bible, the Excel Bible has written a procedure and made it available for download on his site. http://www.j-walk.com/ss search for "nodupes" without the quotes Good Luck TK The procedure at the linked site, which uses a Collection to load a ListBox, is about 8 or 9 times slower than using a Dictionary instead for that purpose, and about 7 times slower than using the ArrayUniques function, which I referred to earlier in this thread, for that purpose. Alan Beban |
Uniqe list
Jason@Simcon wrote:
Hi, There has got to be an easier answer. If you look at it this is a very simple problem. I see people asking here and on newsgroups this type of question all the time and all the answers are like this one. You need a phd to deal with that answer. (or maybe it is just me :) ) Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an impressivley simple problem to solve? Excel needs a formula for just this problem. Just like vlookup or sumif, ETC.... Something like =NOBLANKSNOREPEATS(A1:A50) as an array formula. Do my answers sound frustrated? Trying not to be. Been working on this along time. Thanks to everyone for continuing to try to help. The following is about as simple as can be provided. Array enter it into B1:B50 as =NOBLANKSNOREPEATS(A1:A50). If that is not "impressively simple", then it's you, naively looking for a magic wand. By the way, if you've been working on it unsuccessfully for over a year, and all the answers you see all the time in the newsgroups seem to you to require a PhD. to understand, why in the world would you conclude that it's an impressively simple problem??? Function NOBLANKSNOREPEATS(InputArray) Dim arr, arr2() Dim i As Long Dim Elem, Coll As Collection arr = InputArray Set Coll = New Collection On Error Resume Next For Each Elem In arr Coll.Add Elem, CStr(Elem) If Elem = "" Then Coll.Remove (Elem) Next On Error GoTo 0 ReDim arr2(1 To Coll.Count, 1 To 1) i = 1 For Each Elem In Coll arr2(i, 1) = Elem i = i + 1 Next NOBLANKSNOREPEATS = arr2 End Function Alan Beban |
Uniqe list
Alan Wrote The procedure at the linked site, which uses a Collection to load a ListBox, is about 8 or 9 times slower than using a Dictionary instead for that purpose, and about 7 times slower than using the ArrayUniques function, which I referred to earlier in this thread, for that purpose Alan out of 21 and still counting suggestions you take exception to mine. I didnt reject your suggestion the O.P. did. I was not evaluating these procedures I was suggesting an alternative that he may have a better understanding of. What dont you understand about his reply to you? Alan,. . .your site you sent me to is way over my head. Sorry ............ Jason TK "Alan Beban" wrote: TK wrote: Hi Jason: "Jason@Simcon" wrote: Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an iimpressively simple problem to solve? obviously not :) John Walkenbach the author of the Bible, no not that Bible, the Excel Bible has written a procedure and made it available for download on his site. http://www.j-walk.com/ss search for "nodupes" without the quotes Good Luck TK The procedure at the linked site, which uses a Collection to load a ListBox, is about 8 or 9 times slower than using a Dictionary instead for that purpose, and about 7 times slower than using the ArrayUniques function, which I referred to earlier in this thread, for that purpose. Alan Beban |
Uniqe list
TK wrote:
Alan Wrote The procedure at the linked site, which uses a Collection to load a ListBox, is about 8 or 9 times slower than using a Dictionary instead for that purpose, and about 7 times slower than using the ArrayUniques function, which I referred to earlier in this thread, for that purpose Alan out of 21 and still counting suggestions On review, it seems to be more like 4 suggestions: Advanced Filter|Uniques Only, some sort of query (that I don't understand), specific code, and a pivot table (which I also don't understand). you take exception to mine. Nothing personal. By the time I posted the above comment there was no point in "taking exception to" the Advanced Filter|Uniques Only approach, the query approach, or the specific code -- the OP had already rejected them. I couldn't very well "take exception to" the pivot table suggestion because I don't understand it enough to do so, or to compare its operation to other approaches. Your suggestion was the only one that involved using a Collection Object for generating a unique set of data from a larger set, rather than using a Dictionary Object. It's an area I have only recently become interested in, and I felt (and feel) that the general info about the superiority of the Dictionary Object for this purpose was worth mentioning. If downloading, modifying and implementing John Walkenbach's procedure was not beyond the OP's understanding, or that of any other particular user following this thread, then certainly using a Dictionary Object in that procedure instead of a Collection Object was also not beyond his or their understanding, and would almost certainly be a better choice; I thought that was worth pointing out. And I thought that other users for whom the use of the ArrayUniques function might be less daunting might be interested in the fact that that function seems significantly faster than the Walkenbach procedure (which it is primarily because of its use of the Dictionary Object). I didnt reject your suggestion the O.P. did. I was not evaluating these procedures I was suggesting an alternative that he may have a better understanding of. What dont you understand about his reply to you? Alan,. . .your site you sent me to is way over my head. Sorry Well, e.g., exactly what about the site that it is that he finds way over his head. If his reaction is representative, then knowing more about that reaction might provide an opportunity to simplify or clarify the site for him and others like him. That was part of the reason I provided a follow-up post outlining more precisely the steps that could be utilized to use the data from the site. Sorry for any aspects of my post that caused you to take offense. None was intended. Alan Beban ........... Jason TK "Alan Beban" wrote: TK wrote: Hi Jason: "Jason@Simcon" wrote: Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an iimpressively simple problem to solve? obviously not :) John Walkenbach the author of the Bible, no not that Bible, the Excel Bible has written a procedure and made it available for download on his site. http://www.j-walk.com/ss search for "nodupes" without the quotes Good Luck TK |
Uniqe list
Alan,
Just tried that simple answer. Yes. That is the formula I am looking for. The only problem is that I cant get it to work. Here is what I did In B1 I typed =NOBLANKSNOREPEATS(A1:A50) then I hit CTR+SHFT+ENTR. I am sure by now you already can see I had a problem. It comes up with NAME? What am I doing wrong with this simple formula? "Alan Beban" wrote: Jason@Simcon wrote: Hi, There has got to be an easier answer. If you look at it this is a very simple problem. I see people asking here and on newsgroups this type of question all the time and all the answers are like this one. You need a phd to deal with that answer. (or maybe it is just me :) ) Boiled down the problem is this: from a column of 50, non sorted, entries only 10 are unique, the rest are repetitions. Return in another column the 10 unique entries. Is it me or does that not sound like an impressivley simple problem to solve? Excel needs a formula for just this problem. Just like vlookup or sumif, ETC.... Something like =NOBLANKSNOREPEATS(A1:A50) as an array formula. Do my answers sound frustrated? Trying not to be. Been working on this along time. Thanks to everyone for continuing to try to help. The following is about as simple as can be provided. Array enter it into B1:B50 as =NOBLANKSNOREPEATS(A1:A50). If that is not "impressively simple", then it's you, naively looking for a magic wand. By the way, if you've been working on it unsuccessfully for over a year, and all the answers you see all the time in the newsgroups seem to you to require a PhD. to understand, why in the world would you conclude that it's an impressively simple problem??? Function NOBLANKSNOREPEATS(InputArray) Dim arr, arr2() Dim i As Long Dim Elem, Coll As Collection arr = InputArray Set Coll = New Collection On Error Resume Next For Each Elem In arr Coll.Add Elem, CStr(Elem) If Elem = "" Then Coll.Remove (Elem) Next On Error GoTo 0 ReDim arr2(1 To Coll.Count, 1 To 1) i = 1 For Each Elem In Coll arr2(i, 1) = Elem i = i + 1 Next NOBLANKSNOREPEATS = arr2 End Function Alan Beban |
Uniqe list
Did you first paste that formula into a general module in your workbook?
By the way, although this doesn't account for your #NAME! error, you need to select not just B1 but B1:B50 (or B1:B[a number large enough to accommodate all occurrences of unique values]) before hitting CTR+SHFT+ENTR. Post back if you don't get it to work. Alan Beban Jason@Simcon wrote: Alan, Just tried that simple answer. Yes. That is the formula I am looking for. The only problem is that I cant get it to work. Here is what I did In B1 I typed =NOBLANKSNOREPEATS(A1:A50) then I hit CTR+SHFT+ENTR. I am sure by now you already can see I had a problem. It comes up with NAME? What am I doing wrong with this simple formula? A further "by the way" for any interested followers of this thread. In the interests of "simplicity" I've worked a bit on the ArrayUniques() function to make the process independent of my web site. If you paste the first of the following functions into a general module in your workbook, click on Tools|References and check Microsoft Scripting Runtime, the function can be used to produce an array of unique items from a range or array with more versatility than the simpler NOBLANKSNOREPEATS() function referred to above. The ArrayUniquesLtd() function will work if the number of unique items is less than 5462 or if you are using xl2002 or later. Otherwise, again without resort to my web site, if you paste instead the 2nd and 3rd functions below (again checking the reference to Microsoft Scripting Runtime), the ArrayUniques() function will extract the unique values. Watch for word wrap, particularly in the ArrayTranspose() function: Function ArrayUniquesLtd(InputArray, _ Optional MatchCase As Boolean = True, _ Optional Base_Orient As String = "1vert", _ Optional OmitBlanks As Boolean = True) 'THIS PROCEDURE REQUIRES A PROJECT REFERENCE 'TO "MICROSCOPIC SCRIPTING RUNTIME". 'The function returns an array of unique values 'from an array or range, by default omitting 'blanks and empty strings; to include an empty 'string (or a zero for a blank), use False as 'the 4th parameter. By default the function 'returns a 1-based vertical array; for other 'results enter "0horiz", "1horiz" or "0vert" as 'the 3rd parameter. By default, the function is 'case-sensitive; i.e., e.g., "red" and "RED" are 'treated as two separate unique values; to 'avoid case-sensitivity, enter False as the '2nd parameter. 'Declare the variables Dim arr, arr2 Dim i As Long, p As Object, q As String Dim Elem, x As Dictionary Dim CalledDirectFromWorksheet As Boolean 'For later use in selecting cells for worksheet output CalledDirectFromWorksheet = False If TypeOf Application.Caller Is Range Then Set p = Application.Caller q = p.Address iRows = Range(q).Rows.Count iCols = Range(q).Columns.Count If InStr(1, p.FormulaArray, "ArrayUniques") = 2 _ Or InStr(1, p.FormulaArray, "arrayuniques") = 2 _ Or InStr(1, p.FormulaArray, "ARRAYUNIQUES") = 2 Then CalledDirectFromWorksheet = True End If End If 'Convert an input range to a VBA array arr = InputArray 'Load the unique elements into a Dictionary Object Set x = New Dictionary x.CompareMode = Abs(Not MatchCase) '<--Case-sensitivity On Error Resume Next For Each Elem In arr x.Add Item:=Elem, key:=CStr(Elem) Next If OmitBlanks Then x.Remove ("") On Error GoTo 0 'Load a 0-based horizontal array with the unique 'elements from the Dictionary Object arr2 = x.Items 'This provides appropriate base and orientation 'of the output array Select Case Base_Orient Case "0horiz" arr2 = arr2 Case "1horiz" ReDim Preserve arr2(1 To UBound(arr2) + 1) Case "0vert" arr2 = Application.Transpose(arr2) Case "1vert" ReDim Preserve arr2(1 To UBound(arr2) + 1) arr2 = Application.Transpose(arr2) End Select 'Assure that enough cells are selected to accommodate output If CalledDirectFromWorksheet Then If Range(Application.Caller.Address).Count < x.Count Then ArrayUniquesLtd = "Select a range of at least " & x.Count & " cells" Exit Function End If End If ArrayUniquesLtd = arr2 End Function Function ArrayUniques(InputArray, _ Optional MatchCase As Boolean = True, _ Optional Base_Orient As String = "1vert", _ Optional OmitBlanks As Boolean = True) 'THIS PROCEDURE REQUIRES A PROJECT REFERENCE 'TO "MICROSCOPIC SCRIPTING RUNTIME". 'The function returns an array of unique 'values from an array or range. By default 'it returns a 1-based vertical array; for 'other results enter "0horiz", "1horiz" or '"0vert" as the third argument. By default, 'the function is case-sensitive; i.e., e.g., '"red" and "Red" are treated as two separate 'unique values; to avoid case-sensitivity, 'enter False as the second argument. 'Declare the variables Dim arr, arr2 Dim i As Long, p As Object, q As String Dim Elem, x As Dictionary Dim CalledDirectFromWorksheet As Boolean 'For later use in selecting cells for worksheet output CalledDirectFromWorksheet = False If TypeOf Application.Caller Is Range Then Set p = Application.Caller q = p.Address iRows = Range(q).Rows.Count iCols = Range(q).Columns.Count If InStr(1, p.FormulaArray, "ArrayUniques") = 2 _ Or InStr(1, p.FormulaArray, "arrayuniques") = 2 _ Or InStr(1, p.FormulaArray, "ARRAYUNIQUES") = 2 Then CalledDirectFromWorksheet = True End If End If 'Convert an input range to a VBA array arr = InputArray 'Load the unique elements into a Dictionary Object Set x = New Dictionary x.CompareMode = Abs(Not MatchCase) '<--Case-sensitivity On Error Resume Next For Each Elem In arr x.Add Item:=Elem, key:=CStr(Elem) Next If OmitBlanks Then x.Remove ("") On Error GoTo 0 'Load a 0-based horizontal array with the unique 'elements from the Dictionary Object arr2 = x.Items 'This provides appropriate base and orientation 'of the output array Select Case Base_Orient Case "0horiz" arr2 = arr2 Case "1horiz" ReDim Preserve arr2(1 To UBound(arr2) + 1) Case "0vert" If x.Count < 5461 Or Application.Version 9 Then arr2 = Application.Transpose(arr2) Else arr2 = ArrayTranspose(arr2) End If Case "1vert" ReDim Preserve arr2(1 To UBound(arr2) + 1) If x.Count < 5461 Or Application.Version 9 Then arr2 = Application.Transpose(arr2) Else arr2 = ArrayTranspose(arr2) End If End Select 'Assure that enough cells are selected to accommodate output If CalledDirectFromWorksheet Then If Range(Application.Caller.Address).Count < x.Count Then ArrayUniques = "Select a range of at least " & x.Count & " cells" Exit Function End If End If ArrayUniques = arr2 End Function Function ArrayTranspose(InputArray) 'This function returns the transpose of 'the input array or range; it is designed 'to avoid the limitation on the number of 'array elements and type of array that the 'worksheet TRANSPOSE Function has. 'Declare the variables Dim outputArrayTranspose As Variant, arr As Variant, p As Integer Dim i As Long, j As Long 'Check to confirm that the input array 'is an array or multicell range If IsArray(InputArray) Then 'If so, convert an input range to a 'true array arr = InputArray 'Load the number of dimensions of 'the input array to a variable On Error Resume Next 'Loop until an error occurs i = 1 Do z = UBound(arr, i) i = i + 1 Loop While Err = 0 'Reset the error value for use with other procedures Err = 0 'Return the number of dimensions p = i - 2 End If If Not IsArray(InputArray) Or p 2 Then Msg = "#ERROR! The function accepts only multi-cell ranges and 1D or 2D arrays." If TypeOf Application.Caller Is Range Then ArrayTranspose = Msg Else MsgBox Msg, 16 End If Exit Function End If 'Load the output array from a one- 'dimensional input array If p = 1 Then Select Case TypeName(arr) Case "Object()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Object For i = LBound(outputArrayTranspose) To UBound(outputArrayTranspose) Set outputArrayTranspose(i, LBound(outputArrayTranspose)) = arr(i) Next Case "Boolean()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Boolean Case "Byte()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Byte Case "Currency()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Currency Case "Date()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Date Case "Double()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Double Case "Integer()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Integer Case "Long()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Long Case "Single()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Single Case "String()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1)) As String Case "Variant()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Variant Case Else Msg = "#ERROR! Only built-in types of arrays are supported." If TypeOf Application.Caller Is Range Then ArrayTranspose = Msg Else MsgBox Msg, 16 End If Exit Function End Select If TypeName(arr) < "Object()" Then For i = LBound(outputArrayTranspose) To UBound(outputArrayTranspose) outputArrayTranspose(i, LBound(outputArrayTranspose)) = arr(i) Next End If 'Or load the output array from a two- 'dimensional input array or range ElseIf p = 2 Then Select Case TypeName(arr) Case "Object()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Object For i = LBound(outputArrayTranspose) To _ UBound(outputArrayTranspose) For j = LBound(outputArrayTranspose, 2) To _ UBound(outputArrayTranspose, 2) Set outputArrayTranspose(i, j) = arr(j, i) Next Next Case "Boolean()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Boolean Case "Byte()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Byte Case "Currency()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Currency Case "Date()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Date Case "Double()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Double Case "Integer()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Integer Case "Long()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Long Case "Single()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Single Case "String()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As String Case "Variant()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Variant Case Else Msg = "#ERROR! Only built-in types of arrays are supported." If TypeOf Application.Caller Is Range Then ArrayTranspose = Msg Else MsgBox Msg, 16 End If Exit Function End Select If TypeName(arr) < "Object()" Then For i = LBound(outputArrayTranspose) To _ UBound(outputArrayTranspose) For j = LBound(outputArrayTranspose, 2) To _ UBound(outputArrayTranspose, 2) outputArrayTranspose(i, j) = arr(j, i) Next Next End If End If 'Return the transposed array ArrayTranspose = outputArrayTranspose End Function Alan Beban |
| All times are GMT +1. The time now is 08:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com