Pick the data based on the max value.

[#1]

I have 2 tables.

Table 1:

ID    Amount    Unit

1      100          Office

1      200          Rent

1      300          Warehouse

2      20            Office

2      80            Warehouse

2      100          Rent

Table 2:

ID    Unit

1      Office

1      Warehouse

1      Rent

2      Office

2      Warehouse

2      Rent

Here I have to pick the Unit for ID based the Max amount

My desired output should look like below and also I need Unit as a filter..so they can filter the ID's based on the unit.

ID    Amount    Unit

1      300          Warehouse

2      100          Rent

Thanks much.

[#2]

Here it is .Thank you.

Tabl1:

ID     UnitID        Amount

1          OF           100

1          RE           200

1          WH          300

2          OF           200

2          RE           600

2          WH          500

Table2:

UnitID      Unit

OF           Office

RE           Rent

WH          Warehouse

[trdandamudi]

I just took Sunny's code and modified it:

[vishsaggi]

Sunny's script should work just fine. Another way is as the UnitID is common between two tables autoconcatenation anyway happens here. Probably in the straight table we can get it like

Using pivot table chart.

Dim: ID

Expr as below