按交替编号的行进行排序,而不丢失数据

huangapple go评论76阅读模式
英文:

Sorting by alternate numbered rows without losing data

问题

我想按升序按Sr.No.排序,以便以相同顺序获取名称和ID。由于ID旁边没有编号,我在这样做时遇到了困难,请问有人可以帮忙吗?

英文:

按交替编号的行进行排序,而不丢失数据

I want to sort by Sr.No. in ascending order in such a way that I get both Name and ID in the same order.Since the ID has no numbering alongside I am facing difficulty in doing so, can anyone please help

答案1

得分: 4

在Microsoft 365中,您可以使用:
`=SORTBY(WRAPROWS(B2:B9,2),TOCOL(A2:A9,1))`
或:

=LET(a,A2:B9,
SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1)))

或者如果您想按原样堆叠它们:

=LET(a,A2:B9,
TOCOL(SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1))))


在旧版本的Excel中,您可以使用以下公式(向下/向左拖动):

=IFERROR(INDEX($B$2:$B$9,
MATCH(SMALL($A$2:$A$9,ROW(A1)),
$A$2:$A$9,
0)
+COLUMN(A1)-1),
"")


或者堆叠(向下拖动):

=IFERROR(INDEX($B$2:$B$9,
MATCH(SMALL($A$2:$A$9,INT((1+ROW(A1))/2)),
$A$2:$A$9,
0)
+ISEVEN(ROW(A1))),
"")

英文:

in Microsoft 365 you could use:
=SORTBY(WRAPROWS(B2:B9,2),TOCOL(A2:A9,1))
or:

=LET(a,A2:B9,
SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1)))

Or if you want them stacked the way they were:

=LET(a,A2:B9,
TOCOL(SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1))))

In older Excel you could use the following formula (dragged down/left):

=IFERROR(INDEX($B$2:$B$9,
               MATCH(SMALL($A$2:$A$9,ROW(A1)),
                     $A$2:$A$9,
                     0)
               +COLUMN(A1)-1),
         "")

or stacked (dragged down):

=IFERROR(INDEX($B$2:$B$9,
               MATCH(SMALL($A$2:$A$9,INT((1+ROW(A1))/2)),
                     $A$2:$A$9,
                     0)
               +ISEVEN(ROW(A1))),
         "")

答案2

得分: 4

P.b的回答如此优雅,我无法竞争。

但只是为了记录,我将问题理解为需要将每个人的数据保留在两行,所以得出了以下结果:

=LET(range,A2:B9,
ser,INDEX(range,,1),
fill,SCAN(0,ser,LAMBDA(a,c,IF(c="",a,c))),
sort,SORTBY(range,fill),
IF(sort="", "", sort))
英文:

P.b's answer is so elegant I can't compete.

But just for the record I took the question as requiring each person's data to be kept on two rows so came up with this:

=LET(range,A2:B9,
ser,INDEX(range,,1),
fill,SCAN(0,ser,LAMBDA(a,c,IF(c="",a,c))),
sort,SORTBY(range,fill),
IF(sort="","",sort))

按交替编号的行进行排序,而不丢失数据

答案3

得分: 3

按照每两行排序

在第一个单元格(第2行)中添加一个带有以下公式的辅助列...

=IF(MOD(ROW(),2),C1+1,2*A2-1) 

... 然后向下复制。

现在对这一列进行排序。

编辑

以下是另一种将排序后的数据溢出到另一个位置的方法:

=LET(data,A2:B13,iCol,1, 
i,INDEX(data,,iCol), 
f,IF(i>0,2*i-1,0), 
s,VSTACK(0,DROP(IF(f>0,f+1,0),-1)), 
SORTBY(IF(data="","",data),f+s)) 
英文:

Sort By Every Other Row

按交替编号的行进行排序,而不丢失数据

Add a helper column with the following formula in the first cell (row 2)...

=IF(MOD(ROW(),2),C1+1,2*A2-1)

... and copy down.

Now sort this column instead.

Edit

Here's another way to spill the sorted data in another place:

按交替编号的行进行排序,而不丢失数据

=LET(data,A2:B13,iCol,1,
    i,INDEX(data,,iCol),
    f,IF(i>0,2*i-1,0),
    s,VSTACK(0,DROP(IF(f>0,f+1,0),-1)),
SORTBY(IF(data="","",data),f+s))

huangapple
  • 本文由 发表于 2023年6月22日 20:28:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76531913.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定