Crosstable?

[MK_QSL]

Dear All,

I have below table...

OUR PART

MAIN SUPPLIER

MAIN SUPPLIER PRICE

SUPPLIER 01 Part Number

SUPPLIER 01 PRICE

SUPPLIER 02 Part Number

SUPPLIER 02 PRICE

SUPPLIER 03 Part Number

SUPPLIER 03 PRICE

SUPPLIER 04 Part Number

SUPPLIER 04 PRICE

SUPPLIER 05 Part Number

SUPPLIER 05 PRICE

SUPPLIER 06 Part Number

SUPPLIER 06 PRICE

ABC

MAIN 001

£ 10.00

ABC 01

£ 15.00

ABC 02

£ 8.00

ABC 03

£ 9.00

ABC 04

£ 22.00

ABC 05

£ 21.00

ABC 06

£ 14.00

DEF

MAIN 002

£ 12.00

DEF 01

£ 18.00

DEF 02

£ 7.00

DEF 03

£ 13.00

DEF 04

£ 25.00

DEF 05

£ 18.00

DEF 06

£ 13.00

GHI

MAIN 003

£ 15.00

GHI 01

£ 12.00

GHI 02

£ 16.00

GHI 03

£ 12.00

GHI 04

£ 23.00

GHI 05

£ 17.00

GHI 06

£ 10.00

JKL

MAIN 004

£ 8.00

JKL 01

£ 14.00

JKL 02

£ 12.00

JKL 03

£ 11.00

JKL 04

£ 28.00

JKL 05

£ 16.00

JKL 06

£ 8.00

MNO

MAIN 005

£ 9.00

MNO 01

£ 13.00

MNO 02

£ 11.00

MNO 03

£ 14.00

MNO 04

£ 30.00

MNO 05

£ 15.00

MNO 06

£ 8.00

and wants resut as below table...

OUR PART	MAIN SUPPLIER	MAIN SUPPLIER PRICE	SUPPLIER	SUPPLIER PART Number	SUPPLIER PRICE
ABC	MAIN 001	£ 10.00	SUPPLIER 01	ABC 01	£ 15.00
DEF	MAIN 002	£ 12.00	SUPPLIER 01	DEF 01	£ 18.00
GHI	MAIN 003	£ 15.00	SUPPLIER 01	GHI 01	£ 12.00
JKL	MAIN 004	£ 8.00	SUPPLIER 01	JKL 01	£ 14.00
MNO	MAIN 005	£ 9.00	SUPPLIER 01	MNO 01	£ 13.00
ABC	MAIN 001	£ 10.00	SUPPLIER 02	ABC 02	£ 8.00
DEF	MAIN 002	£ 12.00	SUPPLIER 02	DEF 02	£ 7.00
GHI	MAIN 003	£ 15.00	SUPPLIER 02	GHI 02	£ 16.00
JKL	MAIN 004	£ 8.00	SUPPLIER 02	JKL 02	£ 12.00
MNO	MAIN 005	£ 9.00	SUPPLIER 02	MNO 02	£ 11.00
ABC	MAIN 001	£ 10.00	SUPPLIER 03	ABC 03	£ 9.00
DEF	MAIN 002	£ 12.00	SUPPLIER 03	DEF 03	£ 13.00
GHI	MAIN 003	£ 15.00	SUPPLIER 03	GHI 03	£ 12.00
JKL	MAIN 004	£ 8.00	SUPPLIER 03	JKL 03	£ 11.00
MNO	MAIN 005	£ 9.00	SUPPLIER 03	MNO 03	£ 14.00
ABC	MAIN 001	£ 10.00	SUPPLIER 04	ABC 04	£ 22.00
DEF	MAIN 002	£ 12.00	SUPPLIER 04	DEF 04	£ 25.00
GHI	MAIN 003	£ 15.00	SUPPLIER 04	GHI 04	£ 23.00
JKL	MAIN 004	£ 8.00	SUPPLIER 04	JKL 04	£ 28.00
MNO	MAIN 005	£ 9.00	SUPPLIER 04	MNO 04	£ 30.00
ABC	MAIN 001	£ 10.00	SUPPLIER 05	ABC 05	£ 21.00
DEF	MAIN 002	£ 12.00	SUPPLIER 05	DEF 05	£ 18.00
GHI	MAIN 003	£ 15.00	SUPPLIER 05	GHI 05	£ 17.00
JKL	MAIN 004	£ 8.00	SUPPLIER 05	JKL 05	£ 16.00
MNO	MAIN 005	£ 9.00	SUPPLIER 05	MNO 05	£ 15.00
ABC	MAIN 001	£ 10.00	SUPPLIER 06	ABC 06	£ 14.00
DEF	MAIN 002	£ 12.00	SUPPLIER 06	DEF 06	£ 13.00
GHI	MAIN 003	£ 15.00	SUPPLIER 06	GHI 06	£ 10.00
JKL	MAIN 004	£ 8.00	SUPPLIER 06	JKL 06	£ 8.00
MNO	MAIN 005	£ 9.00	SUPPLIER 06	MNO 06	£ 8.00

Excel file enclosed for your reference.

Please help...

[daveamz]

Hi,

See the file attached.

Regards,

David

[MK_QSL]

As far as my question, this formula working fine. But what about if we have suppliers as having different names instead of SUPPLIER 01, SUPPLIER 02.... etc ...

i.e.

SUPPLIER 01	Germany
SUPPLIER 02	France
SUPPLIER 03	Italy
SUPPLIER 04	Poland
SUPPLIER 05	USA
SUPPLIER 06	UK

[CELAMBARASAN · Accepted solution]

Hi,

Check the attached for solution.

Regards

Celambarasan

[MK_QSL]

Thansk .....