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.

A	B	C
1	dave	yellow
1	dave	red
2	paul	yellow
2	paul	green
3	mark	red
3	mark	red

<colgroup><col width="72" style="width: 54pt;" span="3"> <tbody> </tbody>

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.

A	B	C	D	E
1	dave	yellow	YELLOW	RED
2	paul	yellow	YELLOW	GREEN
3	mark	red	RED	RED

<colgroup><col width="72" style="width: 54pt;" span="5"> <tbody> </tbody>

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
1	1	dave	yellow	=C1	=C2
2	1	dave	red
3	2	paul	yellow	=C3	=C4
4	2	paul	green
5	3	mark	red	=C5	=C6
6	3	mark	red

<tbody>
</tbody>

Sheet1​

 

[sandy666]

try PowerQuery

A	B	C		A	B	Color.1	Color.2
1​	dave	yellow		1​	dave	yellow	red
1​	dave	red		2​	paul	yellow	green
2​	paul	yellow		3​	mark	red	red
2​	paul	green
3​	mark	red
3​	mark	red

[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