Lookup and reference issues

[davel58]

Hi All, i have a connundrum!

let me break it down...

I have 17 lists of names (displayed in columns B, D F, H onwards). I need to assign a number value in a cell next to each of these names from 1-7 (in columns A, C, E, G onwards). this will be manually entered by me.

on a corresponding sheet i would like to have a function that will gather all the names that have a number 1 next to them and transpose them into 1 list. i would like the next column to display the names that have a corresponding 2 next to their name etc etc etc until i get 7 lists.

i am unsure how i can go about this and thank anyone for their help in advance!

 

[repairman615]

Hello,

Fill this down 7 cells, then across as needed:

<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=510><COLGROUP><COL style="WIDTH: 383pt; mso-width-source: userset; mso-width-alt: 9325" width=510><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 383pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=21 width=510>=VLOOKUP(ROW(A1),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A1)-2)),2,FALSE)</TD></TR></TBODY></TABLE>

 

[davel58]

thanks repairman, great formula and it is definitely on the right track!

each list of names contains around 45 names and there will be around 5 "hits" for each number in each list. therefore not all names will have a number assigned to them.

i hope the results will be sorted as follows

List 1
#1 #2 #3
name name name
name name name
etc etc etc

List 2
#1 #2 #3
name name name
name name name
etc etc etc and onwards through 17 lists

the current formula seems to pick up and transpose only those names that have a corresponding #1 next to them and it seems to repeat them as I drag it down further?

i am open to other ways of organising the data as well. it may perhaps be a design fault that is preventing me from getting this to work easily?

thanks again!

 

[repairman615]

It may help to get a better visual of the task. Try the HTML Maker to post a screen shot.

http://www.mrexcel.com/forum/showpos...70&postcount=2

For example, It will post like:

Excel Workbook
	A	B	C	D	E	F
1	1	Joe	7	Benny	2	Connie
2	2	Beth	6	Hal	3	Karl
3	3	Fannie	5	Sally	4	Mac
4	4	Greg	4	Betty	5	Lisa
5	5	Harry	3	Bill	6	Sue
6	6	Flo	2	Frank	7	Jan
7	7	Marg	1	Alice	1	Danny
Sheet1

Excel Workbook
	A	B	C
1	Joe	Alice	Danny
2	Beth	Frank	Connie
3	Fannie	Bill	Karl
4	Greg	Betty	Mac
5	Harry	Sally	Lisa
6	Flo	Hal	Sue
7	Marg	Benny	Jan
Excel 2007 Sheet2

Excel 2007

Cell Formulas
Range	Formula
A1	=VLOOKUP(ROW(A1),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A1)-2)),2,FALSE)
A2	=VLOOKUP(ROW(A2),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A2)-2)),2,FALSE)
A3	=VLOOKUP(ROW(A3),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A3)-2)),2,FALSE)
A4	=VLOOKUP(ROW(A4),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A4)-2)),2,FALSE)
A5	=VLOOKUP(ROW(A5),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A5)-2)),2,FALSE)
A6	=VLOOKUP(ROW(A6),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A6)-2)),2,FALSE)
A7	=VLOOKUP(ROW(A7),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(A7)-2)),2,FALSE)
B1	=VLOOKUP(ROW(B1),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B1)-2)),2,FALSE)
B2	=VLOOKUP(ROW(B2),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B2)-2)),2,FALSE)
B3	=VLOOKUP(ROW(B3),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B3)-2)),2,FALSE)
B4	=VLOOKUP(ROW(B4),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B4)-2)),2,FALSE)
B5	=VLOOKUP(ROW(B5),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B5)-2)),2,FALSE)
B6	=VLOOKUP(ROW(B6),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B6)-2)),2,FALSE)
B7	=VLOOKUP(ROW(B7),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(B7)-2)),2,FALSE)
C1	=VLOOKUP(ROW(C1),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C1)-2)),2,FALSE)
C2	=VLOOKUP(ROW(C2),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C2)-2)),2,FALSE)
C3	=VLOOKUP(ROW(C3),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C3)-2)),2,FALSE)
C4	=VLOOKUP(ROW(C4),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C4)-2)),2,FALSE)
C5	=VLOOKUP(ROW(C5),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C5)-2)),2,FALSE)
C6	=VLOOKUP(ROW(C6),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C6)-2)),2,FALSE)
C7	=VLOOKUP(ROW(C7),OFFSET(Sheet1!$A$1:$B$7,0,(2*COLUMN(C7)-2)),2,FALSE)

Mainly, I am just a bit confused about the result list.
Also will there be several of the same number in the same "Identifier" column?
If so, what results would you expect?

 

[davel58]

hi repairman, my description has been rather confusing.

see the manual example below of what i am trying to acheive;

identifier sheet;

Excel Workbook
	A	B	C	D	E	F
1		List 1		List 2		List 3
2	1	Joe	7	Benny	2	Connie
3	2	Beth	6	Hal	3	Karl
4	3	Fannie	5	Sally	4	Mac
5	4	Greg	4	Betty	5	Lisa
6	5	Harry	3	Bill	6	Sue
7	6	Flo	2	Frank	7	Jan
8	7	Marg	1	Alice	1	Danny
9	1	sean	1	barry	5	david
10	1	rory	1	john	5	mon
11	2	sam	2	sarah	4	will
12	4	bill	4	liz	3	ben
13	4	don	4	penny	1	colin
14	3	james	5	jill	2	sean
15	2	lary	5	james	1	nathan
Sheet1

Excel Workbook
	A	B	C	D	E	F	G
1	Ranking 1	Ranking 2	Ranking 3	Ranking 4	Ranking 5	Ranking 6	Ranking 7
2	joe	beth	fannie	greg	harry	flo	marg
3	sean	sam	james	bill	sally	hal	benny
4	rory	lary	bill	don	jill	sue	jan
5	alice	frank	karl	betty	james
6	barry	sarah	ben	liz	lisa
7	john	connie		penny	david
8	danny	sean		mac	mon
9	colin			will
10	nathan
Excel 2007 the results would then look like the following (i have manually entered them) Sheet2

Excel 2007

does this make more sense?

there will be 7 ranking grades amongst 17 lists of names, each list will have around 45 names in it, however, some names will not have a number assigned to them. as you see above each of the numbers will appear more than once in each list. If i can get this example to work then i will be able to apply it to the actual lists.

very much appreciate your help!

 

[repairman615]

Hello,

How is VBA for you?

If possible, try this.

Press Alt + F11 to open VBEditor
Keystrokes Alt - I - M

Now Paste the below into the module:
(Close window after Paste)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ListByNumbers()<br><br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Crng <SPAN style="color:#00007F">As</SPAN> Range, WorkRng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ResultName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>n = 0<br>i = Application.WorksheetFunction. _<br>        Count(Sheets("Sheet1").Range("A2:AZ2"))<br><br><SPAN style="color:#00007F">Set</SPAN> Crng = Sheets("Sheet1").Range("A2:A" & Range("A2").End(xlDown).Row)<br><br><SPAN style="color:#00007F">For</SPAN> a = 1 <SPAN style="color:#00007F">To</SPAN> i<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> WorkRng = Crng.Offset(0, 2 * n)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> WorkRng<br>        <SPAN style="color:#00007F">If</SPAN> c.Value < 8 And c.Value > 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'''Currently the best I got</SPAN><br>     <br>            ResultName = c.Offset(0, 1).Value<br>               <br>            Sheets("Sheet2").Range("A1").Offset(1000, c.Value - 1).End(xlUp).Offset(1, 0).Value = ResultName<br>                <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">Next</SPAN> c<br>        <br>        n = n + 1<br><SPAN style="color:#00007F">Next</SPAN> a<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


To use, Press Alt + F8. Select ListByNumbers and Run.
Note this uses sheets Named "Sheet1" and "Sheet2".

The raw data is on sheet 1, the results will go on sheet 2. Sheet 2 is blank with only headers in row 1:1.

 

[davel58]

wow, that is awesome. first time i have used VBA!

seems to work absolutely perfectly for the first 7 Lists (taking in data from A1 to N49), but doesn't seem to pick up the rest?

my range of data is A1 to AH49 (17 identifier columns and 17 name lists)

i am a VBA novice so am unsure how to change the range in the VBA module. that is the only thing needed and this will work perfectly!

 

[repairman615]

Welcome to VBA. I am fairly new to that side of excel too.

Through row 2:2 in sheet 1, the macro counts numbers. So do you have numbers every other row through Col. AH? Or, is there a number missing near Column N:N in row 2?

 

[davel58]

excellent work Repairman!

appreciate all your help. It works perfectly when there is a number in all lists beginning column 2:2.

cheers