Format Table (Index/Match)?

[fipebear]

Dear community

I'm having a table with two columns and different rows with repeating variables with hundreds of values. Like this:

Annotation 2020-03-25.png

My goal ist to format it like this and I cannot find out how, since the variables are not repeated regularly per object :-/
Annotation 2020-03-25_2.png

May someone help me with a formula?
(the excel file is attached)

Any help is appreciated!
Thanks a lot.

[azumi]

Put this on E2 and then copied down and cross as necessary

=IFERROR(INDEX($B$2:$B$31,AGGREGATE(15,6,(ROW($B$2:$B$31)-MIN(ROW($B$2:$B$31))+1)/($A$2:$A$31=E$1),ROW(A1))),"")

[Ghozi Alkatiri]

at E2 array formula (Ctrl + shift + enter)
=IFERROR(INDEX($B$1:$B$31,SMALL(IF($A$2:$A$31=$E$1,ROW($A$2:$A$31)),ROW(1:1))+COLUMN(A1)-1),"")

copy cross and down

[fipebear]

Hey guys, Thanks a lot for your help.
But both solution don't work currently. :-( And I have no idea how to solve that.
The first solution is ignoring, that the Objects of are not always the same for each interface.

Object1 has this:
Name interface Port-channel1
description Blalba1
switchport trunk encapsulation dot1q
switchport trunk allowed vlan 10,20,30,40
switchport mode trunk


Object2 has this:
Name interface GigabitEthernet1/0/3
description Blalba4
switchport trunk encapsulation dot1q
switchport trunk allowed vlan 10,20,30,40
switchport mode trunk
channel-group 3 mode on

So for object 2, there is a value which does not exist for Object 1 (channel-group). So I would need a formula, on which I can add values which do not exist for all Objects.

Thank you so much for any help.

[JBeaucaire]

When the data gets this disorganized, I would switch to a VBA solution to simply build the output table each time based on the data it finds in A:B.

[JBeaucaire]

Here we go, no VBA.

1) You will create your row of headers across row 1 starting at E1. Put the possible categories in there in any order you wish. When you spot new possibilities in column A, add a header for that in a new column on row 1.

You can always use an ADVANCED FILTER to copy out the unique values from column A anytime you want to check this row of headers.

2) Now paste your new data into columns A:B

3) Next, two helper columns will be used to keep things easy.

C2 formula: =IF(A2="Name", N(C1)+1, N(C1))
D2 formula: =C2&"-"&A2

4) Copy C2:D2 down to the bottom of the data.

5) Now put this formula in E2 to start the table:

E2: =IFERROR(INDEX($B:$B, MATCH(ROW($A1)&"-"&E$1, $D:$D, 0)),"")

6) Copy E2 across row2 as far as needed.

7) Now copy down as far as needed until you start seeing blank rows.

[fipebear]

Hi JBeaucaire,

Thank you so much for your precious help! This solves my problem perfectly!

Thank you and have a wonderful week!
Take care!