"There is a data table, and some index numbers are stored in the column Header1. In these index n .."

mars RaqForum 25 No.
315 View • 2 Years ago

10.7 Sort by specified order in which duplicate values exist

desktop and excel data processing cases(180)

There is a data table, and some index numbers are stored in the column Header1. In these index numbers, there may be duplicate values, as shown in the red boxes in the figure below, there are multiple 101s:

imagepng

Fill in the data of Header2 and Header3 of the data sheet in columns B and C of the Target sheet respectively. The first 101 of Target sheet corresponds to the data row of the first 101 of data sheet, and the second 101 corresponds to the data row of the second 101. Since there isn’t the third 101 in data sheet, the third 101 in Target sheet will correspond to null value. Calculate in cell B1:

	A
1	=E(‘A1:A10’).derive(:key)
2	=E(‘data!A1:C9’).derive(:key)
3	=A1.group(Header1).run(~.run(key=Header1/“_”/#))
4	=A2.group(Header1).run(~.run(key=Header1/“_”/#))
5	return A2.align(A1:key,key).new(~.Header2,~.Header3)

A1: Convert the passed-in data to a two-dimensional table sequence and add one column key to generate the key value used for alignment.
A2: Convert the passed-in data to a two-dimensional table sequence and add one column key to generate the key value used for alignment.
A3: Group A1 by Header1, loop through every group, and loop through every row in the group. Set the key value to: Header1 + underline + its serial number in the group.
A4: Same as A3.
A5: Align the keys in A1 by the order of the keys in A2, to generate a new data set to return.

imagepng