Excel / VLOOKUP help required

[Slinky]

Folks,

Have googled, but can't find a suitable answer..

Have a set of data that is formatted as follows

	A	B
1	Customer ID	Software Version
2	123	March1
3	123	April1
4	321	March2
5	321	April2
6	456	September1
7	456	October1

I need to carry out a vlookup (or equivalent) to return both records for each customer ID.. Of course, doing a normal vlookup will return the first instance, but not the second..

There are about 1800 rows in the table, so I want to avoid any manual processing where possible..

TIA..

Edited by Slinky on Monday 22 March 16:25

[amir_j]

Simplest way- have 1 column looking up 1 and another column doing the second (ie specifying the next column on to bring back). If you need them together then join them together in a 3rd column. Hide any columns you dont need.

Edited by amir_j on Monday 22 March 16:22

[Slinky]

I've added some cell references Amir, would you care to show your working?

BTW, this needs to be an easily repeatable exercise..

Cheers..

[Piersman2]

What you are appearing to look seems unclear... have you tried pivot tables as you may find them easier.

What exactly do you want the result to look like?

[Liszt]

Can't really do it with Vlookup as that will only return one row.

If you are limited to 2 Software Versions to a Cust Id then may be a simple VBA routine could do this. If it is a true 1 to many relationship you are looking at database functionality and is more comlex.

[amir_j]

Slinky said:

I've added some cell references Amir, would you care to show your working?

BTW, this needs to be an easily repeatable exercise..

Cheers..

Assume you have your data in sheet 1 and the lookup in cell 1 of Sheet2

so Sheet2 B2 would look like =vlookup(A2,Sheet1!A:B,1,0)
and C2 would look like =vlookup(A2,Sheet1!A:B,2,0)

i.e you name both columns in the A:B bit as the range and then the 3rd parameter of 1 or 2 specifies which column to bring back. So B will be your iD's looked up and C will be the names.

[Liszt]

amir_j said:

Slinky said:

I've added some cell references Amir, would you care to show your working?

BTW, this needs to be an easily repeatable exercise..

Cheers..

Assume you have your data in sheet 1 and the lookup in cell 1 of Sheet2

so Sheet2 B2 would look like =vlookup(A2,Sheet1!A:B,1,0)
and C2 would look like =vlookup(A2,Sheet1!A:B,2,0)

i.e you name both columns in the A:B bit as the range and then the 3rd parameter of 1 or 2 specifies which column to bring back. So B will be your iD's looked up and C will be the names.

That only brings back the first row, not all rows though.

[anonymous-user]

Could you combine columns A&B in col c and do the lookup based on column C?

ie. =A2&B2

=Vlookup(what your looking for,$C$2:$C$1800,1,false)

Or do you want to return all references for customerid 123

In that case I'd just use an autofilter on customer id or if you need a separate list run a macro that filters the list and then copy and pastes the data in to a different sheet dependant on what customer id you want.

[Piersman2]

Indeed, OP, can you clarify exactly how you want the result displayed as it kinda effects whether what you want is even possible?

[amir_j]

Liszt said:

amir_j said:

Slinky said:

I've added some cell references Amir, would you care to show your working?

BTW, this needs to be an easily repeatable exercise..

Cheers..

Assume you have your data in sheet 1 and the lookup in cell 1 of Sheet2

so Sheet2 B2 would look like =vlookup(A2,Sheet1!A:B,1,0)
and C2 would look like =vlookup(A2,Sheet1!A:B,2,0)

i.e you name both columns in the A:B bit as the range and then the 3rd parameter of 1 or 2 specifies which column to bring back. So B will be your iD's looked up and C will be the names.

That only brings back the first row, not all rows though.

Doh! teached me to not look at the data table given!

[mrmr96]

Slinky said:

Folks,

Have googled, but can't find a suitable answer..

Have a set of data that is formatted as follows

	A	B
1	Customer ID	Software Version
2	123	March1
3	123	April1
4	321	March2
5	321	April2
6	456	September1
7	456	October1

I need to carry out a vlookup (or equivalent) to return both records for each customer ID.. Of course, doing a normal vlookup will return the first instance, but not the second..

There are about 1800 rows in the table, so I want to avoid any manual processing where possible..

TIA..

Edited by Slinky on Monday 22 March 16:25

Step 1 - Sort by customer ID
Step 2 - Setup a formula in column 2 as follows
C2=if(A3=A2,B2,"") and then copy down the whole table.
Step 3 - Do a vlookup on as follows:
=vlookup(CustomerID,$A$1:$C$7,2,0)&" "&vlookup(CustomerID,$A$1:$C$7,3,0)

Note that this works because Step 2 pulls the Software version from the 2nd row of the client up to the row above. I've only iterated this one time becasue you've only specified that each CustomerID will appear twice at most. If it can occur many times then you'll need a different formula.

Hope this works for what you need.

[Slinky]

Piersman2 said:

Indeed, OP, can you clarify exactly how you want the result displayed as it kinda effects whether what you want is even possible?

That would be handy wouldn't it.. display as ..

Customer Reference | Version 1 | Version 2|

would be nice.. but am happy to see it represented how you see fit..

[anonymous-user]

OK then you need a piece of vba code/macro,

Firstly you need the code to sort the list into customer codes ID's, then you need to run a loop so that for every duplicate customer id in the row below the current one it copys the version and places it to the right of the last filled cell. It will then need to delete the row it has just copied and then loop back until it gets to the end of the list.

Here is the code and it assumes the data is setup exactly as you have shown but for any number of rows. ie. it is just column a & b and the data starts at A2 and there are no gaps in the data.

Sub CustIDandVer()
Dim counter As Integer
Dim counter2 As Integer
Dim fullrange As String
Dim transfervalue As String
Dim location As String

counter = 0

Range("A1").Select

Do Until counter = 1
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
counter = 1
ActiveCell.Offset(-1, 0).Select
ActiveCell.Name = "endofrows"
End If
Loop

Range("endofrows").Select

counter = 0

Do Until counter = 1
ActiveCell.Offset(0, 1).Select
If ActiveCell.Value = "" Then
counter = 1
ActiveCell.Offset(0, -1).Select
ActiveCell.Name = "endofcolumns"
End If
Loop


fullrange = Range("$A$1:endofcolumns").Address

Range(fullrange).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


counter = 0
counter2 = 0

Range("A1").Select

Do Until counter = 1
counter = 0
ActiveCell.Offset(1, 0).Select
location = ActiveCell.Address
If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then
transfervalue = ActiveCell.Offset(1, 1).Value

Do Until counter2 = 1
ActiveCell.Offset(0, 1).Select
If ActiveCell.Value = "" Then
counter2 = 1
ActiveCell.Value = transfervalue
Else
End If
Loop

Range(location).Select
ActiveCell.Offset(1, 0).EntireRow.Delete
counter2 = 0
End If
If ActiveCell.Value = "" Then counter = 1
If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then ActiveCell.Offset(-1, 0).Select
Loop

End Sub

Edited by anonymous-user on Tuesday 23 March 10:26

Edited by anonymous-user on Tuesday 23 March 10:26

[Slinky]

Impressive, thanks chap..

I'll give that a shot later on today if I get a chance..

Just about to post another question I'm afraid.. My head hurts..

[BluePurpleRed]

Slinky said:

Folks,

Have googled, but can't find a suitable answer..

Have a set of data that is formatted as follows

	A	B
1	Customer ID	Software Version
2	123	March1
3	123	April1
4	321	March2
5	321	April2
6	456	September1
7	456	October1

I need to carry out a vlookup (or equivalent) to return both records for each customer ID.. Of course, doing a normal vlookup will return the first instance, but not the second..

There are about 1800 rows in the table, so I want to avoid any manual processing where possible..

TIA..

Edited by Slinky on Monday 22 March 16:25

	A	B	C
1	Customer ID	Software Version	Formula
2	123	March1	Enter the Lookup Value ( i.e. 123 )
3	123	April1	# Occurances
4	321	March2	=COUNTIF(A2:A7, C2)
5	321	April2
6	456	September1	=INDEX($B$2:$B$13, SMALL(IF($A$2:$A$13=C$2,ROW($A$2:$A$13)-ROW($A$2)+1), ROWS(C$6:C6)))
7	456	October1

Its an array formula so you will need to paste it in and then highlight the whole formula and hold Ctrl & Shift and press Enter, this is where you will get the curly braces { } around it to tell you that it has worked.

Then if you drag this down using the bottom right marker ( a little black square on the selected C6 cell as you would any other formula) by the requisite number of cells you will get all of the Software versions you need.

I am starting a new Excel contract in 2 weeks in the City so need to be on top of my game at the moment!

Let me know if you have any problems with this!

[anonymous-user]

BluePurpleRed,

Going slightly OT, can you explain to me how that array forumla works? I've resolved these types issues for years using vba but would much rather use a hardwired formula so that any updated data automatically follows through without having to run a private sub calculate or change event.

Cheers

Edited by anonymous-user on Tuesday 23 March 16:15

[BluePurpleRed]

OneDs said:

BluePurpleRed,

Going slightly OT, can you explain to me how that array forumla works? I've resolved these types issues for years using vba but would much rather use a hardwired formula so that any updated data automatically follows through without having to run a private sub calculate or change event.

Cheers

Edited by OneDs on Tuesday 23 March 16:15

It does work for you right? just checking!

[Slinky]

OneDs said:

BluePurpleRed,

Going slightly OT, can you explain to me how that array forumla works? I've resolved these types issues for years using vba but would much rather use a hardwired formula so that any updated data automatically follows through without having to run a private sub calculate or change event.

Cheers

Edited by OneDs on Tuesday 23 March 16:15

I use this array formula..

=SUMPRODUCT(
SUBTOTAL(3,OFFSET('All - All Partitions'!AB$3,
ROW('All - All Partitions'!AB$3:AB$953)-ROW('All - All Partitions'!AB$3),,1)),

Amongst others on another sheet that is updated daily, works very nicely indeed..

BluePurpleRed, thanks for your suggestion, I'll give that one a go too!

[anonymous-user]

Sorry maybe I wasn't clear, I was potentially hijacking the thread as it was not my original request, I posted up a vba solution previously.

I was interested in your solution as I'd never used array formulea and your option looks like a smarter choice. I was just interested in how it works.

[Slinky]

Just tried that solution BluePurpleRed, works very nicely.. Now just need to work it into what I'm doing.. Cheers...

OneDs, hijack away chap!