...

Align rows of data to a specified column

 

Table A-C has multiple rows of data, where certain rows are empty; and column C has duplicate values. Values of column E are unique; they correspond to certain values of column C, but there are inconsistent letter cases among the corresponding values.


A

B

C

D

E

1

PipeID

Length

Material

Configuration_List

2

Ko

1

plast

Plast

3

Hund

2

concrete

PVC

4



PP

5

Gris

3

plast

6

Lam

4

PVC

7

Fisk

5

PP

We need to align table A-C to column E and ignore case difference.

G

H

I

1

PipeID

Length

Material

2

Ko

1

plast

3

Gris

3

plast

4

Lam

4

PVC

5

Fisk

5

PP

Use SPL XLL to do this:

=spl("=?1.align@a(E@1(?2).(upper(~)),upper(~3)).conj()",A2:C7,E2:E4)

Picture1png

align@a function aligns a sequence to another sequence and performs grouping. conj() function concatenates members of all groups. E@1 converts a multilayer sequence to a single-layer one.

Source:https://stackoverflow.com/questions/78218349/excel-problem-function-that-searches-for-correct-column-based-on-headline-and


 
 •  80 View  •