Make list from multiple lists

[imanew]

I currently have this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	a	1	*	b	4	*	e	7
2	c	2	*	f	2	*	g	2
3	d	1	*	h	3	*	i	5
4	*	*	*	*	*	*	*	*
5	a	*	*	*	*	*	*	*
6	b	*	*	*	*	*	*	*
7	c	*	*	*	*	*	*	*
8	d	*	*	*	*	*	*	*
9	e	*	*	*	*	*	*	*
10	f	*	*	*	*	*	*	*
11	g	*	*	*	*	*	*	*
12	h	*	*	*	*	*	*	*
13	i	*	*	*	*	*	*	*
Sheet1

What i want is column B to be filled with the numbers that relate to the corresponding letter.

 

[tusharm]

In B5 enter =IFERROR(VLOOKUP(A5,$A$1:$B$3,2,FALSE),IFERROR(VLOOKUP(A5,$D$1:$E$3,2,FALSE),VLOOKUP(A5,$G$1:$H$3,2,FALSE)))

Copy B5 as far down as you have data in A.

 

[hiker95]

imanew,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	a	1		b	4		e	7
2	c	2		f	2		g	2
3	d	1		h	3		i	5
4
5	a
6	b
7	c
8	d
9	e
10	f
11	g
12	h
13	i
14
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	a	1		b	4		e	7
2	c	2		f	2		g	2
3	d	1		h	3		i	5
4
5	a	1
6	b	4
7	c	2
8	d	1
9	e	7
10	f	2
11	g	2
12	h	3
13	i	5
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindNumbers()
' hiker95, 04/25/2011
' http://www.mrexcel.com/forum/showthread.php?t=546041
Dim c As Range, rng As Range, d As Range
Application.ScreenUpdating = False
Set rng = Range("A1:H3")
For Each c In Range("A5", Range("A" & Rows.Count).End(xlUp))
  With rng
    Set d = .Find(c, LookIn:=xlValues, LookAt:=xlWhole)
    On Error Resume Next
    c.Offset(, 1).Value = d.Offset(, 1).Value
    On Error GoTo 0
  End With
Next c
Application.ScreenUpdating = True
End Sub

Then run the FindNumbers macro.

 

[imanew]

the only issue with this is that the 3 different columns are on different worksheets. so i cant use "A1:H3" it will need to incorporate all 3 sheets.

 

[imanew]

In B5 enter =IFERROR(VLOOKUP(A5,$A$1:$B$3,2,FALSE),IFERROR(VLOOKUP(A5,$D$1:$E$3,2,FALSE),VLOOKUP(A5,$G$1:$H$3,2,FALSE)))

Copy B5 as far down as you have data in A.

Say there was a column between a and b with data in it how would i get around this?

 

[tusharm]

Why don't you ask the *actual* question you have instead of piecemeal hypotheticals?

Say there was a column between a and b with data in it how would i get around this?

 

[imanew]

How do i get this formula to work if there is data between the two columns? we are looking up. I just figured it out. i just have 3 instead of 2 and extend the array around all three columns

 

[tusharm]

Good for you. Isn't there a certain charm to discovering a solution for yourself?

How do i get this formula to work if there is data between the two columns? we are looking up. I just figured it out. i just have 3 instead of 2 and extend the array around all three columns