Going Loopy

[neilby]

Hi all, struggling with concept of looping so could really do with your help

My problem is this
In one workbook I have a list (indeterminate number) of values which have been data sorted to group duplicates together.

Eg
JC000
JC001
JC001
JC001
JC002
JC002

In another workbook I hold a full list of possible values with associated information

Eg
Column A B C D
Row
132 JB899 Languages Year 2 Manager A
133 JC000 Maths Year 4 Manager B
134 JC001 Science Year 1 Manager C
135 JC002 Science Year 2 Manager C
136 JC003 Science Year 3 Manager C


I need a way to
1. Identify each value from workbook A in turn (and the number of times it appears).
2. Find that value in Workbook B.
3. Pick up the associated data
4. Apply that data in Workbook A to the cells to the right of the initial search value.

Preferred Result =

JC000 Maths Year 4 Manager B
JC001 Science Year 1 Manager C
JC001 Science Year 1 Manager C
JC001 Science Year 1 Manager C
JC002 Science Year 2 Manager C
JC002 Science Year 2 Manager C


Thanks for any help you can give with this.

 

[sanrv1f]

like this?

Excel Workbook
	A	B	C	D
1	JB899	Languages	Year2	ManagerA
2	JC000	Maths	Year4	ManagerB
3	JC001	Science	Year1	ManagerC
4	JC002	Science	Year2	ManagerC
5	JC003	Science	Year3	ManagerC
Sheet2

Excel Workbook
	A	B	C	D
1	JC000	Maths	Year4	ManagerB
2	JC001	Science	Year1	ManagerC
3	JC001	Science	Year1	ManagerC
4	JC001	Science	Year1	ManagerC
5	JC002	Science	Year2	ManagerC
6	JC002	Science	Year2	ManagerC
Sheet3

Cell Formulas
Range	Formula
B1	=VLOOKUP($A1,Sheet2!$A$1:$D$5,COLUMNS($A1:B1),0)
C1	=VLOOKUP($A1,Sheet2!$A$1:$D$5,COLUMNS($A1:C1),0)
D1	=VLOOKUP($A1,Sheet2!$A$1:$D$5,COLUMNS($A1:D1),0)

just curious, why do you need the sets to repeat

 

[neilby]

Thanks for your reply Sankar, I'm looking for a macro solution if possible as I beleive this will be a more 'slimline' system solution as I have 12500 records in Worksheet B and any number of source values in Worksheet A.

I need the information on each line in Worksheet A to allow me to be able to more easily data sort/subtotal Workbook A for management reporting

Neil