help collecting data.

[Guest]

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

 

[Otto Moehrbach]

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

 

[Guest]

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]

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

 

[Guest]

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]

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