Matching Asset List to Unit

Do uou have O365 yet??

That doesn't quite answer my Q. Whatr Excel Product do you want the solution to work for? Your profile shows TWO products... and you have access to O365. So, which is the earliest version you want any solution to work for? If that is ALWAYS the case, then amend your profile accordingly.

I found a solution for O365, but it is difficult (for me) to translate this to a solution for older versions.

Please try in O365:

Please Login or Register  to view this content.

Thanks for the feedback and rep . Glad to have helped.

I also found a solution for Excel 2021 (everything in one go)

Please try in Excel 2021 or in O365:

Please Login or Register  to view this content.

Header for Table3 with just the number

2021 Formula: replace MID(f,g+1,99) with LEFT(f,g-2,99)
365 Formula: replace TEXTAFTER(INDEX(Table1,c-2)," ") with TEXTBEFORE(INDEX(Table1,c-2),".")

Joseph
As requested, I did not rearrange columns and rows. This is can be done either in PQ or in Excel. But all aligns as shown in your expected solution. A bit of a convoluted PQ, however.

Here is a Power Query Solution.

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	D	E	F	G	H	I	J	K
27	No.	Unit	Chair	Table	Table Lamp	Side Table	Sofa	Fan
28	1	1-2	x		x
29	10	12-1				x
30	11	3-3	x
31	12	33-3	x	x		x
32	2	2-2	x		x	x
33	3	23-1		x
34	4	17-2	x	x	x		x
35	5	3-1	x	x				x
36	6	19-3		x
37	7	33-2	x		x		x
38	8	19-2		x	x		x
39	9	21-1		x

Sheet: Sheet1

Thanks for the nice question Joseph and rep again. It was fun and educational to make these formulas. .

I think this is what you are looking for

Please Login or Register  to view this content.

Joseph,
I am unsure of what you are referring. I don't recall hardcoding any fields.

Alternatively is to not reorder the columns or to reorder them alphabetically which I have done in the latest Mcode. Those are the only options I can think of.

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	D	E	F	G	H	I	J	K
27	No.	Unit	Chair	Fan	Side Table	Sofa	Table	Table Lamp
28	1	1-2	x					x
29	2	2-2	x		x			x
30	3	23-1					x
31	4	17-2	x			x	x	x
32	5	3-1	x	x			x
33	6	19-3					x
34	7	33-2	x			x		x
35	8	19-2				x	x	x
36	9	21-1					x
37	10	12-1			x
38	11	3-3	x
39	12	33-3	x		x		x

Sheet: Sheet1

EDIT: Had an after thought and that would be to keep the number of the Asset aligned with the Asset to keep it in the order in the table of Assets. Your thoughts?

I changed this code up a bit to keep the order of the columns in the same order as you have provided in the Asset Table. This will eliminate any "hard coding" or movement of columns

Please Login or Register  to view this content.

Thanks Joseph for the Rep. If this solves your issue, please mark the thread as solved.