vlookup question using more than on variable

[bionicle]

hi all need some help please.

in column 'A' I have a list of numbers with multiple duplicates. in column 'b' I have a list of names, in column 'c' I have a list of colours.
[TABLE="width: 162"]
<colgroup><col width="72" style="width: 54pt;" span="3"> <tbody>[TR]
[TD="width: 72, bgcolor: transparent"]A[/TD]
[TD="width: 72, bgcolor: transparent"]B[/TD]
[TD="width: 72, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[TD="bgcolor: transparent"]yellow[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[TD="bgcolor: transparent"]red[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]paul[/TD]
[TD="bgcolor: transparent"]yellow[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]paul[/TD]
[TD="bgcolor: transparent"]green[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]mark[/TD]
[TD="bgcolor: transparent"]red[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]mark[/TD]
[TD="bgcolor: transparent"]red[/TD]
[/TR]
</tbody>[/TABLE]



what I would like to do in column 'D & E' is to have these values next to each other so 'a1' would stay the same (1) and 'd1' would say yellow & 'E1' would say red.

[TABLE="width: 270"]
<colgroup><col width="72" style="width: 54pt;" span="5"> <tbody>[TR]
[TD="width: 72, bgcolor: transparent"]A[/TD]
[TD="width: 72, bgcolor: transparent"]B[/TD]
[TD="width: 72, bgcolor: transparent"]C[/TD]
[TD="width: 72, bgcolor: transparent"]D[/TD]
[TD="width: 72, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[TD="bgcolor: transparent"]yellow[/TD]
[TD="bgcolor: transparent"]YELLOW[/TD]
[TD="bgcolor: transparent"]RED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]paul[/TD]
[TD="bgcolor: transparent"]yellow[/TD]
[TD="bgcolor: transparent"]YELLOW[/TD]
[TD="bgcolor: transparent"]GREEN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]mark[/TD]
[TD="bgcolor: transparent"]red[/TD]
[TD="bgcolor: transparent"]RED[/TD]
[TD="bgcolor: transparent"]RED[/TD]
[/TR]
</tbody>[/TABLE]


any help would be appreciated.

Steve

 

[The_Macrotect]

Hi Steve,

Are the duplicates always in a row? Is there always one duplicate? If yes on both of these, you could do something like this:

Excel 2007 32 bit

	A	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]dave[/TD]
[TD="align: center"]yellow[/TD]
[TD="align: center"]=C1[/TD]
[TD="align: center"]=C2[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]dave[/TD]
[TD="align: center"]red[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]paul[/TD]
[TD="align: center"]yellow[/TD]
[TD="align: center"]=C3[/TD]
[TD="align: center"]=C4[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]paul[/TD]
[TD="align: center"]green[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]mark[/TD]
[TD="align: center"]red[/TD]
[TD="align: center"]=C5[/TD]
[TD="align: center"]=C6[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]mark[/TD]
[TD="align: center"]red[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

 

[sandy666]

try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]A[/td][td=bgcolor:#5B9BD5]B[/td][td=bgcolor:#5B9BD5]C[/td][td][/td][td=bgcolor:#70AD47]A[/td][td=bgcolor:#70AD47]B[/td][td=bgcolor:#70AD47]Color.1[/td][td=bgcolor:#70AD47]Color.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]dave[/td][td=bgcolor:#DDEBF7]yellow[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]dave[/td][td=bgcolor:#E2EFDA]yellow[/td][td=bgcolor:#E2EFDA]red[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]dave[/td][td]red[/td][td][/td][td]

2​

[/td][td]paul[/td][td]yellow[/td][td]green[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]paul[/td][td=bgcolor:#DDEBF7]yellow[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]mark[/td][td=bgcolor:#E2EFDA]red[/td][td=bgcolor:#E2EFDA]red[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]paul[/td][td]green[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]mark[/td][td=bgcolor:#DDEBF7]red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]mark[/td][td]red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"A", "B"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Color", each Table.Column([Count],"C")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Color", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Color", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Color.1", "Color.2"})
in
    #"Split Column by Delimiter"[/SIZE]

 

[bionicle]

I've never used PowerQuery so how would I incorporate this into excel?

I've downloaded PowerQuery and now have it installed.

 

[Aladin Akyurek]

If you would want to invoke a formula system, would you indicate where the output must go?

 

[sandy666]

I've never used PowerQuery so how would I incorporate this into excel?

I've downloaded PowerQuery and now have it installed.

Select your whole range
Power Query - From Table
Advanced editor (copy code from the post and replece with code in Advanced Editor)
Close&Load

also you can read here

 

[bionicle]

thank you for the step guide, did what you said and received an error message.

Expression.error The column 'A' of the table wasn't found. details A

any ideas

 

[sandy666]

look at movie: Group & Split - from the start to an end

example excel file for this movie

 

[bionicle]

Thanks again for the leads, shall look into them.

 

[sandy666]

You are welcome

PowerQuery works with Tables, columns and content. Each column has header, in this case A, B and C
if you've any diifference you need to adapt it into M code