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.
Top comments (2)
Please free to download SPL XLL for free and explore the data processing journey on your own⬇️
♦️SPL download address: scudata.com/download-Desktop
♦️Plugin Installation Method: c.scudata.com/article/1652061135502
♦️References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
♦️SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...
Stay tuned for more Excel hacks powered by SPL XLL! If you have any challenging Excel tasks, feel free to reach out👇
Discord: discord.gg/hgbKEvJ4
Reddit: reddit.com/r/esProc_Desktop/
Your problem might be the next example we showcase in our series.