Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I need a help in vlookup. Vlookup gives only the first occurrence of a value.

I need all the occurrences in a particular cell. I have attached an excel sheet

with details. Column A and B is the table array and the lookup value is in

column C. Column D has the normal vlookup, however the data i need should be

like how it looks in column E.

I have had NONVBA success with the instructions under "Arbitray Lookup"

in the following:

http://www.cpearson.com/excel/lookups.htm

I tried some things (functions: MATCH & COUNTIF) and have the

following comments and suggested solution.

First, VLOOKUP finds the first occurrance, butalso requires that

the table be sorted so column 1 is in ascending order. This will also

put all same names together which helps obtain your desired result.

Then, the Function COUNTIF can find how many times a Name is in

your table -- and you can get their numbered locatons.

You could do the following.

0- Add another column which is numberd sequentially (just as your

column B happens to be) I'll call it Column "C"

1- Sort the *WHOLE* table (all three columns) by Name, ascending. If

you need it in the original order you can simply "un-sort" by the

column "C" (added in the above step). Or copy the table to another

location or sheet and sort only one of the copies.

2- Use VLOOKUP to locate the first occurrance of a name, in the

*SORTED* list. You'll have to decide just how you want to note this

location. You can add yet another column which is numbered

sequentially AFTER the sort. OR you can use the ROW number of the

first occurrance.

3- Use the function COUNTIF to determine how many of a given Name is

in the Name column of the table.

For example, paste this into your cell "G3":

=COUNTIF(A2:A14,C3)

and copy it down for 4 more cells to obtain a count of each of your

names. It will show values of 1, 2, 3, 3, 2 in your sheet.

4- Use a loop to read the location of the occurrances by looking in

the addeed column "C". Do this for the next "COUNTIF" occurrances;

that is, the number of them given by the COUNTIF.

It will look *Something* like this Pseudo Code:

= = = = = = = = Find items = = = = = = = = = =

Dim Location As Integer

Dim X As Integer

Add sequentially numbered column "C".

Sort *complete* Table By Col "C"

Location = First location (Row) of "Name" found with VLOOKUP

Data_Required = "" 'Start with an empty String.

For X = 0 to [THE COUNTIF value]

Select cell offset from "Location" by X rows. (Offset Method)

Data_Required = Data_Required & ", " & Column "C".Value

' The above builds your numbered list W/ commas.

Next

Save Data_Required String in your column "E" (labeld data Required).

Note:

Although the variable "Location" is a number (Integer) and the

Data_Required" is a String, VBA will do the conversion from number to

String for you in the last line. This allows using the String

Concatination operator "&". If you want to do it explicitly, use the

Convert to String function "Cstr()". The last line then looks like this.

Data_Required = Data_Required & ", " & Cstr(Column "C".Value)

Didn't find what you were looking for?
Find more on Help required in Vlookup
Or get search suggestion and latest updates.

Related Topics:

- Help needed in VLOOKUP
- VLOOKUP in VBA
- using vlookup in userform
- Vlookup in vba when lookup fails
- Newbie VLOOKUP with dates question
- vlookup and error
- vlookup and the value #N/A
- Problem with VLookup in VBA
- VBA Vlookup returning wrong values
- VLookup in VBA
- Vlookup in VBA
- vlookup with two imputs
- VLOOKUP Use in Macros
- How to make WorksheetFunction.MATCH OR .VLOOKUP work?
- Vlookup returns #NA
- Perform a function based on a VLookup
- vlookup with two imputs
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- Help required ( Struts Validation ) Urgently
- Help required to solve one problem related to javax.swing.JTable
- help required regerding making hidden file
- Help Required
- Help required for filtering XML using DOM parser
- help required in RMI
- awt help required