Got something to say or just want fewer pesky ads? Join us... 😊

[Misc] Excel help



Perkino

Well-known member
Dec 11, 2009
5,994
I'd like column C to list all the numbers that have a cell containing an x beneath them in each row.

Numbers 1 2 3 4 5 6 7 8 9 10
Adam Smith x x x x x
Bob Davies x x x
Chris Wright x x x x
David Cook x x x


For example C2 should contain 1, 3, 5, 7, 9. C3 should contain 6, 7, 10. C4 will have 2, 4, 8, 9 and C5 will have 3, 4, 7. I wish to scale this up to around 100 names and 1 - 25 in columns


any useful ideas appreciated
 






maffew

Well-known member
Dec 10, 2003
8,887
Worcester England
Countif?

Sorry that's probably a crap and obvious reply. Tapping away on phone, Im sure someone will have a solution for you quickly clapham gulls very handy in Excel IIRC
 










Acker79

Well-known member
NSC Patron
Nov 15, 2008
31,921
Brighton
I'm not the most advanced user of excel, but am confident. I have a way, but it might be convoluted and more complex than necessary.

In cell N2, O2, P2...
=IF(D2="x", "1, ","") (red number corresponding to the relevant column

Copy and paste into N3, N4, etc.

Then in cell X2 I'd put
=N2 & O2 & P2 & Q2 & R2 & S2 & T2 & U2 & V2 & W2

Then you can hide columns N-W

Screenshot 2020-11-03 at 18.11.51.png
 


clapham_gull

Legacy Fan
Aug 20, 2003
25,501
I'm not the most advanced user of excel, but am confident. I have a way, but it might be convoluted and more complex than necessary.

In cell N2, O2, P2...
=IF(D2="x", "1, ","") (red number corresponding to the relevant column

Copy and paste into N3, N4, etc.

Then in cell X2 I'd put
=N2 & O2 & P2 & Q2 & R2 & S2 & T2 & U2 & V2 & W2

Then you can hide columns N-W

View attachment 130067

That's what I would do and it's a nice solution to break it down and hide columns. There is nothing clever about long formulas, nothing at all.

To get rid of the trailing comma, you could reference X2 in the next column with

=IF(LEN(X2)>0, LEFT(X2 , LEN(X2)-1),"")

But only if you were suffering from a trailing comma syndrome.
 
Last edited:




Perkino

Well-known member
Dec 11, 2009
5,994
I'm not the most advanced user of excel, but am confident. I have a way, but it might be convoluted and more complex than necessary.

In cell N2, O2, P2...
=IF(D2="x", "1, ","") (red number corresponding to the relevant column

Copy and paste into N3, N4, etc.

Then in cell X2 I'd put
=N2 & O2 & P2 & Q2 & R2 & S2 & T2 & U2 & V2 & W2

Then you can hide columns N-W

View attachment 130067

Complex isn't an issue as long as I can type it in correctly. If I decide at a later date to add an x to cell E5 will it automatically update the lists for me? If so then awesome. I shall try and work it out myself when I'm in work tomorrow
 


Acker79

Well-known member
NSC Patron
Nov 15, 2008
31,921
Brighton
Complex isn't an issue as long as I can type it in correctly. If I decide at a later date to add an x to cell E5 will it automatically update the lists for me? If so then awesome. I shall try and work it out myself when I'm in work tomorrow

It should do.
 


Shuggie

Well-known member
Sep 19, 2003
668
East Sussex coast
Two options using "TEXTJOIN".

  1. The first works well and can easily be copied to meet requirements. I have made it tolerant so you don't have to use "x" (Excel can get a bit funny about text matching). See last row which shows you can put any old shit in the cell and it will still work. You can also change the values in the number headings if you choose and that will also work (see last column).
  2. The second formula is actually quite simple [=TEXTJOIN(", ",TRUE,D16:M16)] but requires alll the "x"s to be converted to the value you want displayed. The work required to convert the "x" may not make it worth your while but it's an option.

As attached.
 

Attachments

  • NSC Textjoin v2.xlsx
    13.6 KB · Views: 22
Last edited:




Muhammed - I’m hard - Bruce Lee

You can't change fighters
NSC Patron
Jul 25, 2005
10,862
on a pig farm
I always open these threads but have absolutely no idea why. I’m normally lost after post 3
 




clapham_gull

Legacy Fan
Aug 20, 2003
25,501
Two options using "TEXTJOIN".

  1. The first works well and can easily be copied to meet requirements. I have made it tolerant so you don't have to use "x" (Excel can get a bit funny about text matching). See last row which shows you can put any old shit in the cell and it will still work. You can also change the values in the number headings if you choose and that will also work (see last column).
  2. The second formula is actually quite simple [=TEXTJOIN(", ",TRUE,D16:M16)] but requires alll the "x"s to be converted to the value you want displayed. The work required to convert the "x" may not make it worth your while but it's an option.

As attached.

TEXTJOIN is fine, but only introduced relatively recently to Excel.
 




clapham_gull

Legacy Fan
Aug 20, 2003
25,501
The only difference with the solution I liked, it that I'd use "anchoring" to get at the column header (e.g. "2") rather than hard coding it into the formula.
 


clapham_gull

Legacy Fan
Aug 20, 2003
25,501
My solution attached. The columns in yellow you will effectively hide.

View attachment Book1.xlsx

Just to reiterate, breaking formulas into smaller chunks (and hiding columns if you want) - isn't lazy or dumb.

Far from it, it is best practice and I was taught that by the best. A very well known city accountancy firm whose in-house practice is to ban formulas over a certain length.
 


KZNSeagull

Well-known member
Nov 26, 2007
20,023
Wolsingham, County Durham
My solution attached. The columns in yellow you will effectively hide.

View attachment 130078

Just to reiterate, breaking formulas into smaller chunks (and hiding columns if you want) - isn't lazy or dumb.

Far from it, it is best practice and I was taught that by the best. A very well known city accountancy firm whose in-house practice is to ban formulas over a certain length.

Nice one.
 


clapham_gull

Legacy Fan
Aug 20, 2003
25,501
Nice one.

Nothing worse than a vanity "look how clever I am" formula that no-one else can decipher.

It's moronic.

I'd also apply that to 99% of macros which can easily be achieved with in-built functionality and formulas.

The most stupid I've seen (and I've seen a lot) is a macro that writes formulas.

I used to help out with teaching Excel (with external trainers) as a sort of classroom assistant and helped write a few courses. If I get questions now, if the spreadsheet is over complicated I simply tell them to start again.

It's only clever if it's simple.

I have zero tolerance to spreadsheet vanity as it puts off others from learning.
 






maffew

Well-known member
Dec 10, 2003
8,887
Worcester England
Nothing worse than a vanity "look how clever I am" formula that no-one else can decipher.

It's moronic.

I'd also apply that to 99% of macros which can easily be achieved with in-built functionality and formulas.

The most stupid I've seen (and I've seen a lot) is a macro that writes formulas.

I used to help out with teaching Excel (with external trainers) as a sort of classroom assistant and helped write a few courses. If I get questions now, if the spreadsheet is over complicated I simply tell them to start again.

It's only clever if it's simple.

I have zero tolerance to spreadsheet vanity as it puts off others from learning.

Some of the ridiculous formulas they had on spreadsheets at my place, total CPU burners. People would open them and sit there whilst calculations were updating and all their cores would max out for 15 mins

Ive changed the whole place to pretty much run on views I write on a sql server on a warehouse I designed up for BI/olap. Well Holap really. Excel is mostly a front end now for a bit of power BI
 


Albion and Premier League latest from Sky Sports


Top
Link Here