/wsr/ - Worksafe Requests » Thread #1379742

[9 / 4 / 6]

3KiB, 157x187, example.png

Excel function

Anonymous Mon 24 Jul 2023 16:03:30 No.1379742 View View Reply Original Report

Quoted By: >>1379756 >>1379760 >>1379770

Please help me out here guys, I am nearly giving up...
Like the example shows, I need to write an Excel function that will look into Column B, find its duplicate values and then number them in Column A (starting from 1), only increasing that index for distinct values in Column B

Anonymous

Anonymous Mon 24 Jul 2023 16:43:15 No.1379756 Report

Quoted By:

>>1379742
c = sorted(b)
d = unique_in(c)
e = count(n for n in d)
f = a
g = vlookup(f in e)

This is pseudocode, write the excel functions yourself

Anonymous

Anonymous Mon 24 Jul 2023 16:50:44 No.1379760 Report

Quoted By:

>>1379742
Oh, wait it's the order the value appeared in

c = sorted(b)
d = unique_in(c)
e = match(d in c)
(f,g) = (d,e sorted by e)
h = a
i = match (a in g)

Anonymous

View Same Google ImgOps iqdb SauceNAO real excel sheet.png, 9KiB, 214x790

Anonymous Mon 24 Jul 2023 17:06:16 No.1379770 Report

Quoted By: >>1379772

>>1379742
It's best I clarify some details
For one I can't sort the columns
Secondly there must only be one function, so it then can be copy pasted into the following cells of Column A
Pic related is a screenshot from the actual Excel spreadsheet I am working on - notice how in the bottom of Column B, values start repeating (but their matching value on Colum A must still be equal to their previously assigned values)

Finally this is the best solution I came up with so far (replace the X with row numbers) :
> =IF(BX=B(X-1); A(X-1); IF(BX<>""; A(X-1)+1; ""))
The problem with this obviously the fact I can only look into the previous row, as opposed to all previous rows in search of matching Column B values which would be the ideal

Anonymous

Anonymous Mon 24 Jul 2023 17:08:57 No.1379772 Report

Quoted By: >>1379773

>>1379770
>I can only look into the previous row
Why can't you use MATCH()?

Anonymous

Anonymous Mon 24 Jul 2023 17:19:15 No.1379773 Report

Quoted By:

>>1379772
I tested it (introduced it in B(X-1)) and it then increased all Column A values by 1

Anonymous

View Same Google ImgOps iqdb SauceNAO also the game.png, 32KiB, 1037x674

Anonymous Mon 24 Jul 2023 18:03:57 No.1379791 Report

Quoted By: >>1379819 >>1379828

Put "1" in A1 and this in A2 on downwards:

=IF(ISERROR(INDEX($B1:B$1,MATCH(B2,$B1:B$1,0))),MAX($A$1:A1)+1,INDEX($A$1:A1,MATCH(B2,$B$1:B1,0)))

This checks to see if B2's value exists anywhere above it in column B, and depending on that it either returns the highest previous index +1 or the index number of the cell it matches.

Anonymous

Anonymous Mon 24 Jul 2023 19:00:53 No.1379819 Report

Quoted By: >>1379828

>>1379791
Correction, with the right dollar signs this time:
=IF(ISERROR(INDEX($B$1:B1,MATCH(B2,$B$1:B1,0))),MAX($A$1:A1)+1,INDEX($A$1:A1,MATCH(B2,$B$1:B1,0)))

Anonymous

View Same Google ImgOps iqdb SauceNAO don't open or suffer the punishe (...).gif, 483KiB, 750x1000

Anonymous Mon 24 Jul 2023 19:28:52 No.1379828 Report

Quoted By:

>>1379791
>>1379819
>it works
bless you anon, you're a genius
here have an ass

Capcode	All Only User Posts Only Verified Posts Only Moderator Posts Only Manager Posts Only Admin Posts Only Developer Posts Only Founder Posts
Show Posts	All Only With Images Only Without Images Only Spoiler Images Only Non-Spoiler Images
Deleted Posts	All Only Deleted Posts Only Non-Deleted Posts
Ghost Posts	All Only Ghost Posts Only Non-Ghost Posts
Post Type	All Only Sticky Threads Only Opening Posts Only Reply Posts
Results	All Grouped By Threads
Order	Latest Posts First Oldest Posts First

On these archives

Your latest searches

Excel function