This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
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.
We need to align table A-C to column E and ignore the case difference.
Solution:
Use SPL XLL to do this:
=spl("=?1.align@a(E@1(?2).(upper(~)),upper(~3)).conj()",A2:C7,E2:E4)
As shown in the picture below:
Explanation:
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-07-22T04:07:16+00:00) #30 — Align Rows of Data To A Specified Column. Retrieved from https://www.scien.cx/2024/07/22/30-align-rows-of-data-to-a-specified-column/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.