/wsr/ - Worksafe Requests » Thread #998184

[10 / 3 / ?]

15KiB, 866x256, image.png

Excel: Find Column (Header) by value

Anonymous Mon 29 Mar 2021 13:12:40 No.998184 View View Reply Original Report

Quoted By: >>998191

Hello /wsr/,

I have an Excel problem and I'm wondering if there's an elegant solution to this because other approaches I've seen online are extremely convoluted for what seems like there has to be an easy way to do it.
Pic related has a simplistic example for what I want to achieve:
You enter a value (here below input) and the result should show what column it is in and therefore what category it falls under. So if I were to enter "e" instead, it should give me "Vowels".

Anonymous

Anonymous Mon 29 Mar 2021 13:32:54 No.998188 Report

Quoted By: >>998240

>You enter a value (here below input) and the result should show what column it is in

assuming "Vowels" is in A1

=sumproduct((A2:C6=E4)*column(A2:C6))

sumproduct is an array formula (which you need so you can iterate through all the cells in A2:C6). First term returns true/false on the match ie a lot of zeros and a single one. Second term returns all the column numbers. Then each value gets multiplied with the corresponding value from the other term which zeroes out all the column numbers that are not matches.

General Note: If you know your way around sumproduct it will solve 95% of complex Excel problems for you.

Anonymous

View Same Google ImgOps iqdb SauceNAO excel table.png, 4KiB, 146x225

Anonymous Mon 29 Mar 2021 13:56:49 No.998191 Report

Quoted By: >>998198

>>998184
i tried it in VBA:

open VBA by pressing Alt+F11
open a new module by going to Insert > Module
copy and paste the following code

Function OUTPUT(x) As String

Select Case x

Case Is = "A", "a", "E", "e", "I", "i", "O", "o", "U", "u"
OUTPUT = "Vowel"

Case Is = "Y", "y"
OUTPUT = "Both"

Case Is = BLANK
OUTPUT = BLANK

Case Else
OUTPUT = "Consonant"

End Select

End Function

and then will have a working function is named OUTPUT
go back to excel, click a cell and enter something like =OUTPUT(D2)

Anonymous

Anonymous Mon 29 Mar 2021 14:10:47 No.998198 Report

Quoted By:

>>998191
>go back to excel, click a cell and enter something like =OUTPUT(D2)
and also drag the little square in the corner of the cell as far down as you like, so then you can type in the letters column and it will give an instant result

Anonymous

Anonymous Mon 29 Mar 2021 14:16:49 No.998201 Report

Quoted By: >>998205 >>998212 >>998736

While the Excel gurus are here I have a slightly more advanced question of which I cannot find the answer to anywhere on the internet:
I'm building a simple tool on Excel for price bonding etc...whatever. Simple calculations and basic VBA code you know the stuff.

BUT

All the data is on an access database. Moreover, this database could change. I'm not setting this up for myself, this is a tool that needs to be potentially deployed (except it won't cuz I'm just a retarded uni student) so I need the thing to be

> grandma-proof (aka: GUI based)
> flexible

The database here is technically a variable. I literally cannot find any info on how to get VBA to open a file browser to get the user to select his file through a GUI rather than have him type the file path out, and even then that's totally retarded and makes me lose a ton of time with testing etc.

How do dis pls help 4chan you're my only hope.

Anonymous

View Same Google ImgOps iqdb SauceNAO 00561318fd79ecc9f601324f05c30e14 (...).jpg, 19KiB, 400x400

Anonymous Mon 29 Mar 2021 14:28:09 No.998205 Report

Quoted By: >>998213

>>998201
this seem like what you need?
https://stackoverflow.com/questions/20569959/vba-drag-and-drop-file-to-user-form-to-get-filename-and-path

Anonymous

Anonymous Mon 29 Mar 2021 14:42:09 No.998212 Report

Quoted By: >>998213

>>998201
maybe something like this is better
https://youtu.be/zYLsOq6Vs38

Anonymous

Anonymous Mon 29 Mar 2021 14:43:44 No.998213 Report

Quoted By:

>>998205
>>998212

Oh my God THANK YOU ANON
you just saved my ass.

Now I just need to re-learn how to SQL again.

Anonymous

Anonymous Mon 29 Mar 2021 15:24:56 No.998240 Report

Quoted By:

>>998188
HOLY FUCK THANK YOU
All the solutions to this I've found online were so fucking convoluted but this is extremely simple and I can easily just get the column header by doing match with the column number, thanks a lot!

Anonymous

Anonymous Tue 30 Mar 2021 11:52:43 No.998736 Report

Quoted By:

>>998201
Do not use VBA (just in general) use power query.

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: Find Column (Header) by value