Author Topic: Excel help  (Read 25228 times)

Online Giz a Gobble

  • Scouse Turkey that's hard to swallow.....
  • Main Stander
  • ***
  • Posts: 139
  • JUSTICE FOR THE 96.
Re: Excel help
« Reply #200 on: April 26, 2019, 03:22:23 PM »
Hopefully somebody can help me with this as Iím completely baffled, I managed to do this once and have lost the URL of the page that explained this to me  >:(

Basically I have a list of around 500 numbers excel such as:

123
12234
12345
123456
10989
2818
121313

Etc Etc

What I want to do is organise this so that they are not repeated as above, but instead like:

123; 12234; 12345; 123456; 10989; 2818; 121313

I know thereís a manual way of doing this but I know there is some other way that is more automatic, I also canít remember whether this involves just excel or takes in word as well?

Gah, hopefully somebody can help, thanks in advance!

Offline Stubby!

  • Not on my watch.
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 10,471
  • He asked for it!
Re: Excel help
« Reply #201 on: April 26, 2019, 03:52:57 PM »
You just need them displayed horizontally instead of vertically?  Should be able to just copy and paste special > transpose.  Unless you're looking for something else?
Sit down, shock is better taken with bent knees.

Offline Titi Camara

  • Hey, wanna hear the new dubstep song I wrote? Wub, Wub, Wub! Wubba Lubba Dub Dub! I'm Pickle Rick!
  • RAWK Staff
  • Believer
  • ******
  • Posts: 18,071
  • Number 21 of the Crazy 88
Re: Excel help
« Reply #202 on: April 26, 2019, 03:59:24 PM »
Hopefully somebody can help me with this as Iím completely baffled, I managed to do this once and have lost the URL of the page that explained this to me  >:(

Basically I have a list of around 500 numbers excel such as:

123
12234
12345
123456
10989
2818
121313

Etc Etc

What I want to do is organise this so that they are not repeated as above, but instead like:

123; 12234; 12345; 123456; 10989; 2818; 121313

I know thereís a manual way of doing this but I know there is some other way that is more automatic, I also canít remember whether this involves just excel or takes in word as well?

Gah, hopefully somebody can help, thanks in advance!
If cell A1 contains "123"...

In cell B1 enter =A1&";"

In cell B2 enter =B1&" "&A2&";"

Extend the formula in cell B2 to the end of the data by double clicking in the right hand corner of the cell when a cross appears.

The last populated cell in column B will show the data in the format you require.
I know about the 10+ hoes a week, and itís something Iím willing to do.

Offline Peabee

  • SKPB!
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 9,954
Re: Excel help
« Reply #203 on: May 5, 2019, 02:59:29 AM »
Hopefully somebody can help me with this as Iím completely baffled, I managed to do this once and have lost the URL of the page that explained this to me  >:(

Basically I have a list of around 500 numbers excel such as:

123
12234
12345
123456
10989
2818
121313

Etc Etc

What I want to do is organise this so that they are not repeated as above, but instead like:

123; 12234; 12345; 123456; 10989; 2818; 121313

I know thereís a manual way of doing this but I know there is some other way that is more automatic, I also canít remember whether this involves just excel or takes in word as well?

Gah, hopefully somebody can help, thanks in advance!

as Tubby suggests: copy the cells and 'paste special -> transpose' in another sheet (assuming you want it in a separate sheet).

OR

highlight the same number of cells horizontally and type '=TRANSPOSE(ARRAY)' where ARRAY is your data, then SHIFT+CTRL+ENTER.  You can do this in the same spreadsheet or a new sheet.

I'm assuming you just want your data in a row rather than actually having semi-colons? Otherwise, follow Titi's advice if that's what you require, or type ' =TRANSPOSE(ARRAY)&";" ' in the above.



 
ďWe aren't walking through the storm now - we are the storm.ď Zlen, 30/12/18

Offline PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 12,179
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #204 on: May 17, 2019, 11:23:25 PM »
I am stuck at a problem with excel and was wondering if anyone here could help me with the right formula.
I am creating an excel table for my peer group where they login their working hours, and it will be used to calculate their overtime.

Cell A5 has number of working days in a month. This is of "general" Format.
Cell A6 has worked/clocked hours. This is of "hh:mm:ss" format.

This is where it gets difficult for me.

Let us assume each person has to work 4 hours a day. If a person works for 3 days, it must return 12:00:00. If a person works for 5 days, it must return 20:00:00.

How do I convert the "general" format to "hh:mm:ss" format?
I'll happily come and solve all your Excel issues if I get overtime after four hours a day!
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Online Giz a Gobble

  • Scouse Turkey that's hard to swallow.....
  • Main Stander
  • ***
  • Posts: 139
  • JUSTICE FOR THE 96.
Re: Excel help
« Reply #205 on: June 19, 2019, 02:57:17 PM »
Thanks for the previous help everyone. I've got headbanging issue that I was hoping had a much simpler solution.

I have a spreadsheet with around 11,000 names and telephone numbers ie:

Invoice No   Name  Email Address
1760 John Smith    John@smith.com
1760 John Smith    Smith@John.com
1760 John Smith    Johnnysmith@me.com

1780 Jodie Smith   Jodie@smith.com
1780 Jodie Smith   Smith@john.com
1780 Jodie Smith   Jodiesmith@me.com

What I'm trying to do is merge this all into just two rows and across five rows at the top, ie:

1760   John Smith    John@smith.com   Smith@John.com   Johnnysmith@me.com
1780   Jodie Smith   Jodie@smith.com   Smith@jodie.com  Jodiesmith@me.com

Does this make sense? I've tried multiple things (Vlook up etc) and it isn't getting anywhere. I know about Transforming the range but that is quite manual for each entry and as there are so many, it'll take forever to do one by one...

Offline Dan The Man 28373

  • Kopite
  • *****
  • Posts: 821
  • We Always win in Rome!!
Re: Excel help
« Reply #206 on: June 19, 2019, 05:42:33 PM »
Thanks for the previous help everyone. I've got headbanging issue that I was hoping had a much simpler solution.

I have a spreadsheet with around 11,000 names and telephone numbers ie:

Invoice No   Name  Email Address
1760 John Smith    John@smith.com
1760 John Smith    Smith@John.com
1760 John Smith    Johnnysmith@me.com

1780 Jodie Smith   Jodie@smith.com
1780 Jodie Smith   Smith@john.com
1780 Jodie Smith   Jodiesmith@me.com

What I'm trying to do is merge this all into just two rows and across five rows at the top, ie:

1760   John Smith    John@smith.com   Smith@John.com   Johnnysmith@me.com
1780   Jodie Smith   Jodie@smith.com   Smith@jodie.com  Jodiesmith@me.com

Does this make sense? I've tried multiple things (Vlook up etc) and it isn't getting anywhere. I know about Transforming the range but that is quite manual for each entry and as there are so many, it'll take forever to do one by one...

It's how you set up the raw data.  If you have the original table set up as 5 rows by 3 columns (new data added to a new column of 5 rows).

                   Column
Row             A                 B                                C                               
1                 Invoice No    1760                           1780
2                 Name         John Smith                   Jodie Smith
3                 Email         John@smith.com           Jodie@smith.com
4                                 smith@john.com           smith@jodie.com
5                                 johnnysmith@me.com   jodiesmith@me.com

So if you copy & transpose that original table, you will get it in the format you want

Invoice No            Name                                          Email
1760                    John Smith                                  John@smith.com               smith@john.com               johnnysmith@me.com
1780                    Jodie Smith                                 Jodie@smith.com               smith@jodie.com              jodiesmith@me.com

Not sure if this will help you though if you can't easily change the layout of your original data.

Offline Red_Bear

  • Main Stander
  • ***
  • Posts: 190
  • We all Live in a Red and White Kop
Re: Excel help
« Reply #207 on: June 20, 2019, 07:47:28 AM »
Are there always 3 rows for each person in the initial data, or does that vary? Either way, Iím pretty sure I can come up with something that should do it for you, but itíll be slightly different depending on that.

Offline -Willo-

  • -the wisp-
  • Believer
  • ******
  • Posts: 12,662
Re: Excel help
« Reply #208 on: June 22, 2019, 08:34:15 AM »
I don't know how to explain this so sorry if this is awful, I need to create a sheet on excel that leads the user to the end based on what they select.

So they begin with OPTION1, OPTION 2, OPTION 3.

And if they select OPTION 1, then this brings them to another bunch of options, and this repeats a few times until it finishes. I've tried to lay it out below.


OPTION 1             OPTION 2               OPTION 3

*SELECTS OPTION 1*

OPTION 1.1        OPTION 1.2            OPTION 1.3

*SELECTIONS OPTION 1.1*

OPTION 1.11       OPTION 1.12          OPTION 1.13           OPTION 1.14

*SELECTIONS OPTION 1.12*

OPTION 1.121        OPTION 1.122         OPTION 1.123        OPTION 1.1234


ETC...

Is this possible?

Offline Thush

  • Spawwow, Tit. Anal Chat is "Equidistant between chit-chat and analysis"
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 2,898
  • It's pronounced "Toosh"
Re: Excel help
« Reply #209 on: June 22, 2019, 09:46:12 AM »
Something like this sounds like what you want.

https://trumpexcel.com/dependent-drop-down-list-in-excel/

Online Tepid T₂O

  • MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of conviction
  • Lead Matchday Commentator
  • Believer
  • ******
  • Posts: 66,011
  • Dejan Lovren fan club member #1
Re: Excel help
« Reply #210 on: June 22, 2019, 10:03:34 AM »
Ohh... thatís useful
Bernard blows goats

With courage, nothing is impossible.

"My right arm hurts - I don't know why or who hit me."

Offline PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 12,179
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #211 on: June 22, 2019, 10:10:10 AM »
Something like this sounds like what you want.

https://trumpexcel.com/dependent-drop-down-list-in-excel/
The Trump excels. The biggliest Trump. The bestest Trump.
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline -Willo-

  • -the wisp-
  • Believer
  • ******
  • Posts: 12,662
Re: Excel help
« Reply #212 on: June 22, 2019, 10:24:54 AM »
Something like this sounds like what you want.

https://trumpexcel.com/dependent-drop-down-list-in-excel/

Thank you, much appreciated.

Will give this a go, mine is going to be drop down lists within drop down lists so hopefully I don't get confused halfway ;D

Online Giz a Gobble

  • Scouse Turkey that's hard to swallow.....
  • Main Stander
  • ***
  • Posts: 139
  • JUSTICE FOR THE 96.
Re: Excel help
« Reply #213 on: June 24, 2019, 10:56:55 AM »
Thanks everyone, managed to sort it in the end!  :D

Offline CHOPPER

  • Bad Tranny with a Chopper. Hello John gotta new Mitre? I'm Jim Davidson in disguise. Undercover Cop (Grammar Division). Does Louis Spence. Well. A giga-c*nt worth of nothing in particular. Hodgson apologist. Astronomical cock.
  • Believer
  • ******
  • Posts: 49,287
  • Bring on the Champions! Bring on the Champions!
  • Super Title: Not Arsed
Re: Excel help
« Reply #214 on: July 2, 2019, 03:54:40 PM »
sorted now.



 
« Last Edit: July 2, 2019, 05:58:26 PM by CHOPPZBOT »
@ Veinticinco de Mayo The way you talk to other users on this forum is something you should be ashamed of as someone who is suppose to be representing the site.
Martin Kenneth Wild - Part of a family

Offline jackh

  • Has a blog but doesn't like to talk about it.
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 8,064
    • @hartejack
Re: Excel help
« Reply #215 on: August 3, 2019, 12:51:56 PM »
I'm getting a message saying one of my .xlsx files cannot be opened: "file format or file extension is not valid - verify that the file has not been corrupted and that the file extension matches the format of the file".

Still struggling to open this, despite having run through a few quick suggestions I found ... any ideas?

Offline Liverlee

  • Daddy Discord
  • Believer
  • ******
  • Posts: 2,151
Re: Excel help
« Reply #216 on: October 9, 2019, 04:26:09 PM »
Hi All

I'm in need of some help - i'm trying to use conditional formatting to highlight individual cell when a word is typed. So if i start to type the word google - the cell containing the word google highlights red with a yellow font.

So if i type g or goo the cell containing the word google is highlighted.

In the past i've used this formula =IF(ISBLANK($G$3), 0,SEARCH($G$3,$B5&$C5&$D5&$E5&$F5)) which works a treat to highlight a row.

but i just want to highlight a single cell rather than an entire row.

My search box is in cell G3

the only cells that contain any text are in
C5,C7,C9,C11, C14, C16, C18, C20
E5,E7,E9,E11, E14, E16, E18, E20
G5,G7,G9,G11, G14, G16, G18, G20
I5,I7,I9,I11, I14, I16, I18,I20.

All these cells are actually hyperlinks, so if you click on C5 it will take you to google, if you click on c7 it will take you to mrexcel, c9 youtube etc etc.

The spreadsheet is just hyperlinks or blank cells.
The spreadsheet goes from cell B4 to J21.

Any advice or guidance gratefully received.


Offline PaulF

  • -.-- -. .-- .-
  • RAWK Supporter
  • Believer
  • ******
  • Posts: 12,179
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #217 on: October 9, 2019, 05:04:35 PM »
Are we allowed to use VBA macros for this?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline RobbieRedman

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 8,581
  • RAWK Cheltenham 2018 Champion Tipster
Re: Excel help
« Reply #218 on: January 7, 2020, 01:35:39 PM »
Hello

I'm trying to use data validation for a drop down selection where you can choose more than one option. From my research i think the user will need to go back and choose option 2, then again go back and choose any more if need be, no problem.

However the vba macros i have tried just dont work

so is there a way i can do this without vba and just a formula>?

thanks

Online Tepid T₂O

  • MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of conviction
  • Lead Matchday Commentator
  • Believer
  • ******
  • Posts: 66,011
  • Dejan Lovren fan club member #1
Re: Excel help
« Reply #219 on: January 7, 2020, 02:32:15 PM »
Canít you just validate the cells and define it as a list?

Then you type in your list comma separated below
Bernard blows goats

With courage, nothing is impossible.

"My right arm hurts - I don't know why or who hit me."

Offline RobbieRedman

  • RAWK Supporter
  • Believer
  • ******
  • Posts: 8,581
  • RAWK Cheltenham 2018 Champion Tipster
Re: Excel help
« Reply #220 on: January 7, 2020, 02:44:13 PM »
Canít you just validate the cells and define it as a list?

Then you type in your list comma separated below
Cheers mate, I think a manual approach is what is needed anyway!