Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help collecting data.
hello,
another question from the official excel newbie! any and all help is greatly appriceated guys! So I have this list i'm always talking about that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example product a 10.99 345353 product a 11.99 3445453 product a 10.99 657567 product a 8.99 345353 product a 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the above is just an example) , I want excel to search for all entries matching the product entered (A1:A2000) .. If it finds the products I would like it to display what order numbers are tied to that product number, maybe seprated by commas? so if i put in D1 that i'm searching for 'PRODUCTA' it would return something like this... 345353, 3445453, 657567, 345353, 5464646 showing me all the orders for which that product was used... is this possible and if so, how? Thanks alot guys! Brandon Roland |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help collecting data.
Brandon
The first thing that comes to mind is to use Data - Filter - AutoFilter. Look that up in Help. The result would be that all the data rows in the spreadsheet would be hidden except those rows that match your criteria. If you want the result displayed in any other way or in another place in the sheet or the file, you will need VBA. Post back with more detail about what you want. Otto "brandon roland" wrote in message ... hello, another question from the official excel newbie! any and all help is greatly appriceated guys! So I have this list i'm always talking about that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example product a 10.99 345353 product a 11.99 3445453 product a 10.99 657567 product a 8.99 345353 product a 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the above is just an example) , I want excel to search for all entries matching the product entered (A1:A2000) .. If it finds the products I would like it to display what order numbers are tied to that product number, maybe seprated by commas? so if i put in D1 that i'm searching for 'PRODUCTA' it would return something like this... 345353, 3445453, 657567, 345353, 5464646 showing me all the orders for which that product was used... is this possible and if so, how? Thanks alot guys! Brandon Roland |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help collecting data.
Thanks Otto! Sorry I wasnt more specific.. but i'm creating an entire excel
program that does various things so the filter isnt really an option. I want to type in cell D1, for instance, the product string i'm looking for .. Product codes are listed in A1:A2000 (2,000 different products) , I want a code that can search for all instances on the string typed in D1 (only in column A) .. If it finds the string in Column A, I want it to gather the info adjacent in column C (say its A2000, I want it to give me C2000) BUT... there are multiple product items. I'm basically doing a search for a product, and I want it to tell me all the order's that included that product (order numbers are in column C) , So say there are two instances of PRODUCTA in A1:A2000, it would take each of there respective values in column C and display them both, seperated by a comma "452342,3423423" But there is up to 5 different order numbers so the script would need the ability to find and display up to five different numbers. for instance.... COL A COL B COL C PRODUCTA 10.99 23423324 PRODUCTA 8.99 45345345 PRODUCTB 9.99 23423423 PRODUCTA 10.99 453453453 now if i type "PRODUCTA" in D1, I would like D2 to display " 23423324, 45345345, 453453453 " because those are the three order numbers that PRODUCTA was used in.. If there was only 1 instance of PRODUCTA, it would only display "234234," so on an so forth.. Thanks again I hope you guys can help me out i'm excel-tarded. "Otto Moehrbach" wrote: Brandon The first thing that comes to mind is to use Data - Filter - AutoFilter. Look that up in Help. The result would be that all the data rows in the spreadsheet would be hidden except those rows that match your criteria. If you want the result displayed in any other way or in another place in the sheet or the file, you will need VBA. Post back with more detail about what you want. Otto "brandon roland" wrote in message ... hello, another question from the official excel newbie! any and all help is greatly appriceated guys! So I have this list i'm always talking about that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example product a 10.99 345353 product a 11.99 3445453 product a 10.99 657567 product a 8.99 345353 product a 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the above is just an example) , I want excel to search for all entries matching the product entered (A1:A2000) .. If it finds the products I would like it to display what order numbers are tied to that product number, maybe seprated by commas? so if i put in D1 that i'm searching for 'PRODUCTA' it would return something like this... 345353, 3445453, 657567, 345353, 5464646 showing me all the orders for which that product was used... is this possible and if so, how? Thanks alot guys! Brandon Roland |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
help collecting data.
Brandon
You say you want the entries in Column C with commas between them, but you don't say where you want them. Apparently you don't want to just see them since you say that AutoFilter is not an option. You say you are creating an entire Excel program but you don't give any specifics about it. If the only option is to have the numbers in a string separated by commas (and a space??) then you will need VBA. I don't know if you are using VBA in your program so I don't know if you want the result placed in a VBA variable or if you want the result placed in some cell. If so, what cell? I don't mean to be picky, but computers are dumb critters and have to be told everything. Otto "brandon roland" wrote in message ... Thanks Otto! Sorry I wasnt more specific.. but i'm creating an entire excel program that does various things so the filter isnt really an option. I want to type in cell D1, for instance, the product string i'm looking for . Product codes are listed in A1:A2000 (2,000 different products) , I want a code that can search for all instances on the string typed in D1 (only in column A) .. If it finds the string in Column A, I want it to gather the info adjacent in column C (say its A2000, I want it to give me C2000) BUT... there are multiple product items. I'm basically doing a search for a product, and I want it to tell me all the order's that included that product (order numbers are in column C) , So say there are two instances of PRODUCTA in A1:A2000, it would take each of there respective values in column C and display them both, seperated by a comma "452342,3423423" But there is up to 5 different order numbers so the script would need the ability to find and display up to five different numbers. for instance.... COL A COL B COL C PRODUCTA 10.99 23423324 PRODUCTA 8.99 45345345 PRODUCTB 9.99 23423423 PRODUCTA 10.99 453453453 now if i type "PRODUCTA" in D1, I would like D2 to display " 23423324, 45345345, 453453453 " because those are the three order numbers that PRODUCTA was used in.. If there was only 1 instance of PRODUCTA, it would only display "234234," so on an so forth.. Thanks again I hope you guys can help me out i'm excel-tarded. "Otto Moehrbach" wrote: Brandon The first thing that comes to mind is to use Data - Filter - AutoFilter. Look that up in Help. The result would be that all the data rows in the spreadsheet would be hidden except those rows that match your criteria. If you want the result displayed in any other way or in another place in the sheet or the file, you will need VBA. Post back with more detail about what you want. Otto "brandon roland" wrote in message ... hello, another question from the official excel newbie! any and all help is greatly appriceated guys! So I have this list i'm always talking about that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example product a 10.99 345353 product a 11.99 3445453 product a 10.99 657567 product a 8.99 345353 product a 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the above is just an example) , I want excel to search for all entries matching the product entered (A1:A2000) .. If it finds the products I would like it to display what order numbers are tied to that product number, maybe seprated by commas? so if i put in D1 that i'm searching for 'PRODUCTA' it would return something like this... 345353, 3445453, 657567, 345353, 5464646 showing me all the orders for which that product was used... is this possible and if so, how? Thanks alot guys! Brandon Roland |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
help collecting data.
Otto,
thanks again for the replies.. They can be in any CELL , I figured i'd post the code in the cell that I want the order numbers(info from column C displayed) I didnt post any other specifics of the program because they are irrelevant to the task I'm trying to preform. I simple want to put a alphanumeric string into a cell (say D1), the code will search A1:A2000 for all instances of the string that is in D1 . If the string is found in A1, it will copy the data in C1, say that the string is found in A212, it would copy the data in C212 ... If it is found in both A1 and A212, it would copy both values.. for up to 5 values, and display them in a cell seperated by comma's or spaces, whichever is easier. Doesnt seem to hard to grasp... Thanks for your help i'll try posting in the programming forum. BR "Otto Moehrbach" wrote: Brandon You say you want the entries in Column C with commas between them, but you don't say where you want them. Apparently you don't want to just see them since you say that AutoFilter is not an option. You say you are creating an entire Excel program but you don't give any specifics about it. If the only option is to have the numbers in a string separated by commas (and a space??) then you will need VBA. I don't know if you are using VBA in your program so I don't know if you want the result placed in a VBA variable or if you want the result placed in some cell. If so, what cell? I don't mean to be picky, but computers are dumb critters and have to be told everything. Otto "brandon roland" wrote in message ... Thanks Otto! Sorry I wasnt more specific.. but i'm creating an entire excel program that does various things so the filter isnt really an option. I want to type in cell D1, for instance, the product string i'm looking for . Product codes are listed in A1:A2000 (2,000 different products) , I want a code that can search for all instances on the string typed in D1 (only in column A) .. If it finds the string in Column A, I want it to gather the info adjacent in column C (say its A2000, I want it to give me C2000) BUT... there are multiple product items. I'm basically doing a search for a product, and I want it to tell me all the order's that included that product (order numbers are in column C) , So say there are two instances of PRODUCTA in A1:A2000, it would take each of there respective values in column C and display them both, seperated by a comma "452342,3423423" But there is up to 5 different order numbers so the script would need the ability to find and display up to five different numbers. for instance.... COL A COL B COL C PRODUCTA 10.99 23423324 PRODUCTA 8.99 45345345 PRODUCTB 9.99 23423423 PRODUCTA 10.99 453453453 now if i type "PRODUCTA" in D1, I would like D2 to display " 23423324, 45345345, 453453453 " because those are the three order numbers that PRODUCTA was used in.. If there was only 1 instance of PRODUCTA, it would only display "234234," so on an so forth.. Thanks again I hope you guys can help me out i'm excel-tarded. "Otto Moehrbach" wrote: Brandon The first thing that comes to mind is to use Data - Filter - AutoFilter. Look that up in Help. The result would be that all the data rows in the spreadsheet would be hidden except those rows that match your criteria. If you want the result displayed in any other way or in another place in the sheet or the file, you will need VBA. Post back with more detail about what you want. Otto "brandon roland" wrote in message ... hello, another question from the official excel newbie! any and all help is greatly appriceated guys! So I have this list i'm always talking about that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example product a 10.99 345353 product a 11.99 3445453 product a 10.99 657567 product a 8.99 345353 product a 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the above is just an example) , I want excel to search for all entries matching the product entered (A1:A2000) .. If it finds the products I would like it to display what order numbers are tied to that product number, maybe seprated by commas? so if i put in D1 that i'm searching for 'PRODUCTA' it would return something like this... 345353, 3445453, 657567, 345353, 5464646 showing me all the orders for which that product was used... is this possible and if so, how? Thanks alot guys! Brandon Roland |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
help collecting data.
Brandon
Here is a macro to get you started. I assumed that you have 5 columns of data starting in Column A. Row 2 has your headers and your data starts in Row 3. The item you want to search for in Column A is in A1. The result is placed in D1. HTH Otto Sub GetValues() Dim TheRng As Range Dim i As Range Dim GetC As String Application.ScreenUpdating = False Set TheRng = Range("A2", Range("A" & Rows.Count).End(xlUp)) TheRng.Resize(, 5).AutoFilter Field:=1, Criteria1:=Range("A1").Value GetC = "" Set TheRng = Range(TheRng(2), TheRng(TheRng.Count)) For Each i In TheRng.Offset(, 2).SpecialCells(xlCellTypeVisible) GetC = GetC & i.Value & ", " Next i TheRng.Resize(, 5).AutoFilter Range("D1").Value = Left(GetC, Len(GetC) - 2) Application.ScreenUpdating = True End Sub "brandon roland" wrote in message ... Otto, thanks again for the replies.. They can be in any CELL , I figured i'd post the code in the cell that I want the order numbers(info from column C displayed) I didnt post any other specifics of the program because they are irrelevant to the task I'm trying to preform. I simple want to put a alphanumeric string into a cell (say D1), the code will search A1:A2000 for all instances of the string that is in D1 . If the string is found in A1, it will copy the data in C1, say that the string is found in A212, it would copy the data in C212 ... If it is found in both A1 and A212, it would copy both values.. for up to 5 values, and display them in a cell seperated by comma's or spaces, whichever is easier. Doesnt seem to hard to grasp... Thanks for your help i'll try posting in the programming forum. BR "Otto Moehrbach" wrote: Brandon You say you want the entries in Column C with commas between them, but you don't say where you want them. Apparently you don't want to just see them since you say that AutoFilter is not an option. You say you are creating an entire Excel program but you don't give any specifics about it. If the only option is to have the numbers in a string separated by commas (and a space??) then you will need VBA. I don't know if you are using VBA in your program so I don't know if you want the result placed in a VBA variable or if you want the result placed in some cell. If so, what cell? I don't mean to be picky, but computers are dumb critters and have to be told everything. Otto "brandon roland" wrote in message ... Thanks Otto! Sorry I wasnt more specific.. but i'm creating an entire excel program that does various things so the filter isnt really an option. I want to type in cell D1, for instance, the product string i'm looking for . Product codes are listed in A1:A2000 (2,000 different products) , I want a code that can search for all instances on the string typed in D1 (only in column A) .. If it finds the string in Column A, I want it to gather the info adjacent in column C (say its A2000, I want it to give me C2000) BUT... there are multiple product items. I'm basically doing a search for a product, and I want it to tell me all the order's that included that product (order numbers are in column C) , So say there are two instances of PRODUCTA in A1:A2000, it would take each of there respective values in column C and display them both, seperated by a comma "452342,3423423" But there is up to 5 different order numbers so the script would need the ability to find and display up to five different numbers. for instance.... COL A COL B COL C PRODUCTA 10.99 23423324 PRODUCTA 8.99 45345345 PRODUCTB 9.99 23423423 PRODUCTA 10.99 453453453 now if i type "PRODUCTA" in D1, I would like D2 to display " 23423324, 45345345, 453453453 " because those are the three order numbers that PRODUCTA was used in.. If there was only 1 instance of PRODUCTA, it would only display "234234," so on an so forth.. Thanks again I hope you guys can help me out i'm excel-tarded. "Otto Moehrbach" wrote: Brandon The first thing that comes to mind is to use Data - Filter - AutoFilter. Look that up in Help. The result would be that all the data rows in the spreadsheet would be hidden except those rows that match your criteria. If you want the result displayed in any other way or in another place in the sheet or the file, you will need VBA. Post back with more detail about what you want. Otto "brandon roland" wrote in message ... hello, another question from the official excel newbie! any and all help is greatly appriceated guys! So I have this list i'm always talking about that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example product a 10.99 345353 product a 11.99 3445453 product a 10.99 657567 product a 8.99 345353 product a 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the above is just an example) , I want excel to search for all entries matching the product entered (A1:A2000) .. If it finds the products I would like it to display what order numbers are tied to that product number, maybe seprated by commas? so if i put in D1 that i'm searching for 'PRODUCTA' it would return something like this... 345353, 3445453, 657567, 345353, 5464646 showing me all the orders for which that product was used... is this possible and if so, how? Thanks alot guys! Brandon Roland |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collecting data from one sheet to another | Excel Discussion (Misc queries) | |||
Collecting data from worksheets. | Excel Worksheet Functions | |||
collecting data from various worksheets | Excel Worksheet Functions | |||
collecting data | Excel Discussion (Misc queries) | |||
Help with collecting data from a spreadsheet | Excel Discussion (Misc queries) |