VLOOKUP Help?

[jmattingly85]

I am needing some assistance with VLOOKUP, and that might not even be what I need to use. I have an Excel spreadsheet of data that currently looks as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]NAME A[/TD]
[TD]code 1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]NAME A[/TD]
[TD]code 2[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]NAME A[/TD]
[TD]code 3[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]NAME B[/TD]
[TD]code 3[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]NAME B[/TD]
[TD]code 2[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]NAME C[/TD]
[TD]code 2[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]NAME C[/TD]
[TD]code 3[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]NAME C[/TD]
[TD]code 1[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]


What I need to is get all of the data in Column B to be in columns D, E and F by their code description as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Amount[/TD]
[TD]Code 1[/TD]
[TD]Code 2[/TD]
[TD]Code 3[/TD]
[/TR]
[TR]
[TD]NAME A[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]NAME B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]NAME C[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]67[/TD]
[TD]56[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible to do fairly easy?

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Code[/td][td=bgcolor:#5B9BD5]Amount[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]code 1[/td][td=bgcolor:#70AD47]code 2[/td][td=bgcolor:#70AD47]code 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NAME A[/td][td=bgcolor:#DDEBF7]code 1[/td][td=bgcolor:#DDEBF7]

12​

[/td][td][/td][td=bgcolor:#E2EFDA]NAME A[/td][td=bgcolor:#E2EFDA]

12​

[/td][td=bgcolor:#E2EFDA]

13​

[/td][td=bgcolor:#E2EFDA]

81​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NAME A[/td][td]code 2[/td][td]

13​

[/td][td][/td][td]NAME B[/td][td][/td][td]

98​

[/td][td]

56​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NAME A[/td][td=bgcolor:#DDEBF7]code 3[/td][td=bgcolor:#DDEBF7]

81​

[/td][td][/td][td=bgcolor:#E2EFDA]NAME C[/td][td=bgcolor:#E2EFDA]

12​

[/td][td=bgcolor:#E2EFDA]

67​

[/td][td=bgcolor:#E2EFDA]

56​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NAME B[/td][td]code 3[/td][td]

56​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NAME B[/td][td=bgcolor:#DDEBF7]code 2[/td][td=bgcolor:#DDEBF7]

98​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NAME C[/td][td]code 2[/td][td]

67​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NAME C[/td][td=bgcolor:#DDEBF7]code 3[/td][td=bgcolor:#DDEBF7]

56​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NAME C[/td][td]code 1[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[jmattingly85]

That's exactly it.

 

[sandy666]

ok, so you can use PowerQuery aka Get&Transform with M-code like this:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[COLOR="#FF0000"][ Code][/COLOR]), "Code", "Amount")
in
    #"Pivoted Column"[/SIZE]

there is space in [ Code] because tag name is the same. Remove this space if you will use it in the PQ

example excel file

 

[jmattingly85]

I guess I'm not even sure where to begin with that. I downloaded your example file and don't see where you put that code in.

 

[sandy666]

look at Data tab on the ribbon.

if you see Get&Transform, use Show Queries then dbl click on the table there, it will open PQ editor, find Advanced Editor on the ribbon and you'll see the M-code from the post

if you don't see Get&Transform then .... you can ignore my posts

 

[jmattingly85]

I do see that.

 

[sandy666]

so you've PQ

see how it was done

 

[DanteAmor]

Create a pivot table. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways.

1. Select the table or cells (including column headers) containing the data you want to use.
2. From the Insert tab, click the PivotTable command.

3. The Create PivotTable dialog box will appear.
3.1. Select option: Existing worksheet
3.2. Select cell E2
3.3. Press Ok
4. A blank PivotTable and Field List will appear.

5. Drag field between areas:

Ready, let me know if you have any doubts.