[Misc] Excel help

[Perkino]

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

 

[Perkino]

View attachment Book1.xlsxView attachment Book1.xlsx

I clearly don't have the tech skills to get this working

 

[maffew]

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

 

[Weststander]

Lotus 1-2-3 is the way forward.

 

[The Antikythera Mechanism]

Lotus 1-2-3 is the way forward.

I prefer Supercalc 5

 

[maffew]

Lotus 1-2-3 is the way backwards.

Corrected

 

[Acker79]

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

 

[clapham_gull]

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.

 

[Perkino]

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]

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]

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.

 

[Muhammad - I’m hard - Bruce Lee]

I always open these threads but have absolutely no idea why. I’m normally lost after post 3

 

[KZNSeagull]

I have the attached solution where the data is on rows not columns. Cannot get it to work with columns somehow. It may help.

View attachment nsc.xlsx

 

[clapham_gull]

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]

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]

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]

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]

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.

 

[Norman Potting]

I always open these threads but have absolutely no idea why. I’m normally lost after post 3

Glad it's not just me

 

[maffew]

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