VLOOKUP? help

A

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
Hi,

I have a rather large spreadsheet that is basically a matrix of computer specs and software requirements 110 columns by 300 rows. What I need is another row that does a concatenate type function which is currently setup as =A3&CHAR(10)&B3&CHAR(10)&..... etc.. this works fine, but to keep things visable in the matrix of software I need to have the value = X of the Cells where the column header is the software name I'll try and show this below:


1 A B C D E
2 OFFICE WINZIP ADOBE READER ADOBE FLASH CONCATENATE SW LIST
3 X X oFFICE WINZIP
4 X X OFFICE ADOBE READER
5 X X X OFFICE WINZIP FLASH


So basically in the concatenate column I need the formula that will do a lookup of the headings in row 2 when there is an X in the column. so =(If A3 = X, VLOOKUP(A2))&....


does that make any sense?

Thanks for any pointers
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Armster said:
Hi,

I have a rather large spreadsheet that is basically a matrix of computer specs and software requirements 110 columns by 300 rows. What I need is another row that does a concatenate type function which is currently setup as =A3&CHAR(10)&B3&CHAR(10)&..... etc.. this works fine, but to keep things visable in the matrix of software I need to have the value = X of the Cells where the column header is the software name I'll try and show this below:


1 A B C D E
2 OFFICE WINZIP ADOBE READER ADOBE FLASH CONCATENATE SW LIST
3 X X oFFICE WINZIP
4 X X OFFICE ADOBE READER
5 X X X OFFICE WINZIP FLASH


So basically in the concatenate column I need the formula that will do a lookup of the headings in row 2 when there is an X in the column. so =(If A3 = X, VLOOKUP(A2))&....


does that make any sense?

Thanks for any pointers
Click to expand...

Add the following function code in VBA to your workbook as a module.

In order to add the function to your workbook, run...

ALt+F11
Insert | Module
File | Close and Return to Microsoft Excel.
_________________________________________________________

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
________________________________________________________

Given...

<TABLE style="WIDTH: 421pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=562><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4039" width=114><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3584" width=101><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 7793" width=219><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 85pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=114> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 164pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=219> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>OFFICE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>WINZIP</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ADOBE READER</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ADOBE FLASH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Concated SW List</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OFFICE,WINZIP</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OFFICE,WINZIP</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OFFICE,WINZIP,ADOBE READER</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

Now equipped with ACONCAT...

E3, control+shift+enter, not just enter, and copy down:

=SUBSTITUTE(aconcat(IF(A3:D3="X",","&$A$2:$D$2,"")),",","",1)
 
Upvote 0
I think I may possibly love you Aladin! :)

This worked fantastic. I can't seem to work out (I'm most likely being completely dense) whch bit to change If I want to replace the , with CHAR(010) to put each piece of software on to a new line within the cell.

Its to go in to a form which the staff sign off saying this is the software I want on my PC.. type thing.. :)

Thanks Again

you've helped me alot already
 
Upvote 0
Sorted it out thanks, was trying to put it in the wrong place....

The cell entry look like the following now:


=SUBSTITUTE(aconcat(IF(CM3:CP3="X",CHAR(10)&$CM$2:$CP$2,"")),","," ",1)


thanks Again Aladin for your help
 
Upvote 0
Armster said:
I think I may possibly love you Aladin! :)

This worked fantastic. I can't seem to work out (I'm most likely being completely dense) whch bit to change If I want to replace the , with CHAR(010) to put each piece of software on to a new line within the cell.

Its to go in to a form which the staff sign off saying this is the software I want on my PC.. type thing.. :)

Thanks Again

you've helped me alot already
Click to expand...

Control+shift+enter, not just enter:

=SUBSTITUTE(aconcat(IF(A3:D3="X",CHAR(10)&$A$2:$D$2,"")),CHAR(10),"",1)

and activate Wrap text for the formula cell then copy down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
317
Mouse7681
M
D
  • Solved
VLOOKUP or INDEX/MATCH utilizing day of the week
Replies
5
Views
567
Darren_workforce
D
M
  • Question Question
VLOOKUP?
Replies
1
Views
218
MeaclH
M
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
275
MeaclH
M
M
  • Question Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
751
MountainFog
M

Forum statistics

Threads
1,224,562
Messages
6,179,526
Members
452,923
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top