Question description:
This user has given permission to use the problem statement for this
blog.
I have an excel sheet with thousands of lines.... Sheet expands to the right for 2 years worth of data... Wanting to know if its possible to mark each line item with a color/etc with the first time data is present... very hard to expain.
Solved by I. J. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/10/2018 - 03:48
Hello welcome to got it pro
User
11/10/2018 - 03:48
hi shall i upload my file so i can show you what i am looking for?
Excelchat Expert
11/10/2018 - 03:49
yes that was much better :)
User
11/10/2018 - 03:49
ok how to do it lol
Excelchat Expert
11/10/2018 - 03:50
just click this green icon beside the chatbox
Excelchat Expert
11/10/2018 - 03:50
to upload it
User
11/10/2018 - 03:51
hmm
[Uploaded an Excel file]
User
11/10/2018 - 03:51
how to add that?
User
11/10/2018 - 03:51
all i have is download option
User
11/10/2018 - 03:51
no upload file
Excelchat Expert
11/10/2018 - 03:52
you have uploaded it already :)
Excelchat Expert
11/10/2018 - 03:52
i checking on it now
User
11/10/2018 - 03:52
i cant see the document on my screen
User
11/10/2018 - 03:53
says blank.xlsx
Excelchat Expert
11/10/2018 - 03:53
it was only here on the chatbox
Excelchat Expert
11/10/2018 - 03:53
not on the livesheet
User
11/10/2018 - 03:54
hmm no way to show my xls on this page eh?
User
11/10/2018 - 03:54
clicking sheets just takes me to a place that says download
Excelchat Expert
11/10/2018 - 03:54
wait it try to display it there
Excelchat Expert
11/10/2018 - 03:55
or you can just copy your data and paste it there
User
11/10/2018 - 03:55
ok ill move some data over
Excelchat Expert
11/10/2018 - 03:55
ok :)
User
11/10/2018 - 03:55
ok so this is just a small part
User
11/10/2018 - 03:56
i have 14000 lines down
User
11/10/2018 - 03:56
my question is this
Excelchat Expert
11/10/2018 - 03:56
ok what is it
User
11/10/2018 - 03:56
u see column J and to the right?
User
11/10/2018 - 03:57
is there a way to take all the data from J and to the right and mark the cell that first shows data?
User
11/10/2018 - 03:57
example
User
11/10/2018 - 03:57
line 7
User
11/10/2018 - 03:58
u see how J on line 7 shows 0
User
11/10/2018 - 03:58
Z has the first thing that is not a 0
User
11/10/2018 - 03:59
i want that to be highlighted... this way i know which Month that company started having data
User
11/10/2018 - 03:59
i want that same logic for every line
Excelchat Expert
11/10/2018 - 04:00
ok so you want to highlight those 1st zero?
User
11/10/2018 - 04:00
no i want to highligh the first cell that does NOT have a 0
User
11/10/2018 - 04:01
like that
User
11/10/2018 - 04:01
so now i now that this company starting using my services on 1-17
User
11/10/2018 - 04:02
line 8 would mark that one... becuase all 0's before
User
11/10/2018 - 04:02
now i know company on line 8 started 02-18
Excelchat Expert
11/10/2018 - 04:02
ah ok i get it
User
11/10/2018 - 04:03
i need a way to do this for my entire sheet which is 14000+ lines down
Excelchat Expert
11/10/2018 - 04:04
ok just give me a minute to work on this
User
11/10/2018 - 04:05
thanks...and if i can know what you did so i can do it on my main sheet that would be great
Excelchat Expert
11/10/2018 - 04:05
yes i will discuss it to you once done
User
11/10/2018 - 04:10
appreciate it
Excelchat Expert
11/10/2018 - 04:34
hello
Excelchat Expert
11/10/2018 - 04:34
does your problem, is specific only in highlighting the cell?
Excelchat Expert
11/10/2018 - 04:35
or its ok to you to pull up the date where do you have the first data
Excelchat Expert
11/10/2018 - 04:36
hello are you still there?
User
11/10/2018 - 04:36
im here
Excelchat Expert
11/10/2018 - 04:36
great
Excelchat Expert
11/10/2018 - 04:37
are you able to see the last column per each line?
Excelchat Expert
11/10/2018 - 04:37
the column DB
User
11/10/2018 - 04:37
yes i see it
Excelchat Expert
11/10/2018 - 04:37
i put a lookup there
User
11/10/2018 - 04:37
so this tell me what month this account first had data?
Excelchat Expert
11/10/2018 - 04:37
yes
Excelchat Expert
11/10/2018 - 04:38
does much easier than highlighting the cells
User
11/10/2018 - 04:38
awesome so how do i do this?
Excelchat Expert
11/10/2018 - 04:38
i've used the formula
Excelchat Expert
11/10/2018 - 04:38
=INDEX($J$1:$DA$1,MATCH(TRUE,INDEX(J2:DA2>0,0),))
User
11/10/2018 - 04:39
im not an excel expert so how do i actually use that formula?
Excelchat Expert
11/10/2018 - 04:39
wherein it will lookup first the first data in a row, then after looking on it, it will lookup the date where that column belong
Excelchat Expert
11/10/2018 - 04:40
you can copy that formula and paste it in all end of the line of your data
Excelchat Expert
11/10/2018 - 04:40
just copy paste it and formula will follow on each line
User
11/10/2018 - 04:41
so copy =INDEX($J$1:$DA$1,MATCH(TRUE,INDEX(J2:DA2>0,0),))
Excelchat Expert
11/10/2018 - 04:41
yes
User
11/10/2018 - 04:41
and paste it where?
Excelchat Expert
11/10/2018 - 04:41
on the last column of each line
Excelchat Expert
11/10/2018 - 04:41
like we did now
User
11/10/2018 - 04:41
i did
User
11/10/2018 - 04:41
but all lines show 9-16
Excelchat Expert
11/10/2018 - 04:42
just paste the formula on the first line
User
11/10/2018 - 04:42
ok i must be doing somethign wrong
User
11/10/2018 - 04:42
i right click copied =INDEX($J$1:$DA$1,MATCH(TRUE,INDEX(J2:DA2>0,0),))
Excelchat Expert
11/10/2018 - 04:42
then once you copy the formula there, copy the cell and paste it on the below line
User
11/10/2018 - 04:43
right click on cell DB top
User
11/10/2018 - 04:43
paste
Excelchat Expert
11/10/2018 - 04:43
yes it wrong
User
11/10/2018 - 04:43
09-16 bill now shows in cell DB and that its
User
11/10/2018 - 04:43
haha
Excelchat Expert
11/10/2018 - 04:43
it will show all line as line1 :)
Excelchat Expert
11/10/2018 - 04:44
just paste the formula on the first line
Excelchat Expert
11/10/2018 - 04:44
then once you paste the formula there
User
11/10/2018 - 04:44
it doesnt work
Excelchat Expert
11/10/2018 - 04:44
i will show you how
User
11/10/2018 - 04:44
ok thanks
Excelchat Expert
11/10/2018 - 04:44
just look on our live sheet
User
11/10/2018 - 04:44
ok
User
11/10/2018 - 04:45
when i right click paste on DB
User
11/10/2018 - 04:45
on my sheet
User
11/10/2018 - 04:45
i dont get that
Excelchat Expert
11/10/2018 - 04:46
dont right click on the whole column
Excelchat Expert
11/10/2018 - 04:46
just paste the formula on the first line
Excelchat Expert
11/10/2018 - 04:46
then try to drag it
User
11/10/2018 - 04:46
i did
Excelchat Expert
11/10/2018 - 04:46
down
User
11/10/2018 - 04:46
i wish you could see my actual sheet
Excelchat Expert
11/10/2018 - 04:46
point your mouse on the corner of the cell where you paste the formula
Excelchat Expert
11/10/2018 - 04:47
[Uploaded an Excel file]
Excelchat Expert
11/10/2018 - 04:47
you can just check it here
Excelchat Expert
11/10/2018 - 04:47
if the issue doesn't resolve is it ok for you to raise it again so we can continue working on it?
Excelchat Expert
11/10/2018 - 04:48
thanks, by the way could I ask your kind feedback after our session?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
