Question description:
This user has given permission to use the problem statement for this
blog.
I'm using a formula to reference a closed spreadsheet but I only want it to return the data in the column not repeat the data from the start
Solved by A. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/03/2018 - 04:56
Hi
Excelchat Expert
10/03/2018 - 04:56
How are you
Excelchat Expert
10/03/2018 - 04:57
nd how may i assist you with today
User
10/03/2018 - 04:57
I'm good for 4am Sydney time
Excelchat Expert
10/03/2018 - 04:57
OOO, wake up too early
User
10/03/2018 - 04:57
im writing a number of formulas
User
10/03/2018 - 04:57
no still up late
Excelchat Expert
10/03/2018 - 04:57
Okay seems to be hard working
User
10/03/2018 - 04:58
hang on ill show the syntax
Excelchat Expert
10/03/2018 - 04:58
sure go ahead
Excelchat Expert
10/03/2018 - 04:58
m in india 11pm, working hard
User
10/03/2018 - 04:59
ok
Excelchat Expert
10/03/2018 - 05:00
Okay thats a referencing from different file i guess?
User
10/03/2018 - 05:00
so I put the correct file path and it returns the data in a column but if I copy down the column it keeps repeating the data
User
10/03/2018 - 05:00
it returns the data in the list then after it has returned all the data there is it starts returning from the top again
User
10/03/2018 - 05:01
I only want it to return the data from the list one time
Excelchat Expert
10/03/2018 - 05:01
while referencing it would have have been great if you have shared the sheet
Excelchat Expert
10/03/2018 - 05:02
can you now share me both the sheets?
User
10/03/2018 - 05:02
example if there are 10 names in a column and I use this formula and copy it down the whole column in the sheet where I want the return it doesn't stop after returning the ten names it keeps repeating down the column
User
10/03/2018 - 05:03
i cant share as its my wifes work but i can re create here'
Excelchat Expert
10/03/2018 - 05:04
yes that will workout for me, please go ahead
Excelchat Expert
10/03/2018 - 05:04
Names ae in column A,? right
User
10/03/2018 - 05:04
one moment
Excelchat Expert
10/03/2018 - 05:05
Okay
User
10/03/2018 - 05:06
well the idea is to use the example sheets here and the list of names in sheet 2
User
10/03/2018 - 05:07
then return the values in sheet 1
Excelchat Expert
10/03/2018 - 05:08
Okay
Excelchat Expert
10/03/2018 - 05:09
its seems the index formula is not working properly in shared sheet
Excelchat Expert
10/03/2018 - 05:09
wait i will share you the excel file with this example
User
10/03/2018 - 05:10
maybe it will help if i explain what im trying to do more
Excelchat Expert
10/03/2018 - 05:10
yes sure please go ahead
User
10/03/2018 - 05:11
i have a file with multiple tabs all of which have the same header title for a certain data
Excelchat Expert
10/03/2018 - 05:11
Okay
User
10/03/2018 - 05:12
i want to extract all the data from each tab in the column that matches the header title then return that data in one column on another spreadsheet
Excelchat Expert
10/03/2018 - 05:12
then i guess Vlookup clause would work well the
User
10/03/2018 - 05:12
so each list of names for example from each tab in one column after each other
User
10/03/2018 - 05:13
ive been told index match would work better
User
10/03/2018 - 05:13
but i don't mind as long as it does the job
Excelchat Expert
10/03/2018 - 05:13
yes, aggregation of index match = vlookup
User
10/03/2018 - 05:14
can you write me a formula that allows me to do this
User
10/03/2018 - 05:14
and then i can just change the file path myself
Excelchat Expert
10/03/2018 - 05:14
vlookup is the simplest form of index and match
Excelchat Expert
10/03/2018 - 05:14
okay
Excelchat Expert
10/03/2018 - 05:14
sure will do that
User
10/03/2018 - 05:14
thanks
Excelchat Expert
10/03/2018 - 05:15
See in sheet 2 different name have different values
User
10/03/2018 - 05:16
why do they have values at all
Excelchat Expert
10/03/2018 - 05:16
now for example we want to look up dave's value in sheet1
Excelchat Expert
10/03/2018 - 05:16
its for example
Excelchat Expert
10/03/2018 - 05:17
Now check in sheet 1
User
10/03/2018 - 05:17
ok so if i can explain using this example i just want to take the list of names from sheet 2 and return them in sheet 1 but with no duplicates
Excelchat Expert
10/03/2018 - 05:18
Okay
User
10/03/2018 - 05:18
obviously this is a simple version and i would change the formula so it will take the list from another spreadsheet that is also closed sheet
Excelchat Expert
10/03/2018 - 05:20
Now check in Sheet 1, i guess this is the issue you might be facing while copying to other cells, right?
User
10/03/2018 - 05:21
i see nothing in sheet 1
User
10/03/2018 - 05:21
oh ok
User
10/03/2018 - 05:21
wait
User
10/03/2018 - 05:21
well that's a start
User
10/03/2018 - 05:22
but i want it to return the whole list and only return each name once
User
10/03/2018 - 05:22
so no duplicates
Excelchat Expert
10/03/2018 - 05:22
see now
User
10/03/2018 - 05:23
getting there
User
10/03/2018 - 05:23
now i want tit to return only each name once
Excelchat Expert
10/03/2018 - 05:23
yes like john appeard twice you dont want that right?
User
10/03/2018 - 05:23
yes
Excelchat Expert
10/03/2018 - 05:26
it can done using the remove duplicate option
User
10/03/2018 - 05:26
what is that
User
10/03/2018 - 05:26
need the formula to do it automatically
Excelchat Expert
10/03/2018 - 05:27
yes i understand trying to evaluate it
User
10/03/2018 - 05:29
is there a simple way to copy the whole column and then auto remove duplicates after that
Excelchat Expert
10/03/2018 - 05:30
For that you would need vba code,
User
10/03/2018 - 05:30
oh i c
User
10/03/2018 - 05:30
im trying to get away from VBA
Excelchat Expert
10/03/2018 - 05:30
still will come up with a formula
User
10/03/2018 - 05:31
will use it if i need to but that means getting someone to help write it
User
10/03/2018 - 05:32
you see there are some other processes i need to do as well after i extract the list
Excelchat Expert
10/03/2018 - 05:32
Okay
User
10/03/2018 - 05:33
maybe i can add to this spreadsheet to show more of the example
Excelchat Expert
10/03/2018 - 05:34
yes just randomize your data and share it
User
10/03/2018 - 05:34
ok
User
10/03/2018 - 05:36
this data is time in hours minutes seconds
Excelchat Expert
10/03/2018 - 05:36
okay
Excelchat Expert
10/03/2018 - 05:39
this is the main file right?
Excelchat Expert
10/03/2018 - 05:39
where there is all the data
User
10/03/2018 - 05:39
yes so ill do just one tab but there are multiple tabs
Excelchat Expert
10/03/2018 - 05:40
okay
Excelchat Expert
10/03/2018 - 05:41
So you need all these data uniquely in other sheet, right?
Excelchat Expert
10/03/2018 - 05:43
Okay i got it, for this you would just 1 formula in sheet 3 ie: Sumifs
Excelchat Expert
10/03/2018 - 05:44
And for names i can see you would need unique values
User
10/03/2018 - 05:44
yeah i can make the sumifs but i need to be able to expand the cell to show the sub totals
User
10/03/2018 - 05:44
ill show you
Excelchat Expert
10/03/2018 - 05:47
So you just need the unique names from the list right,?
User
10/03/2018 - 05:49
so i need to sum each name separately that way they oinly appear once in sheet 3 with a total for that name then a total for all the names
User
10/03/2018 - 05:49
but i need this list of names in sheet 3 to be like an expandable cell
User
10/03/2018 - 05:50
this is for my wifes work and she is asleep so i cant ask but i understand what she wants
User
10/03/2018 - 05:50
she described it like a pivot table in sheet 3
User
10/03/2018 - 05:50
so the data is like "sort and filter"
User
10/03/2018 - 05:51
or appears like a pivot table and you can do the same as sort and filter
Excelchat Expert
10/03/2018 - 05:51
yes, also it should be able to add unique names
User
10/03/2018 - 05:51
yes sum each unique name from sheet one and give it 1 total for that name in sheet 3
User
10/03/2018 - 05:52
thn do this for each unique name
User
10/03/2018 - 05:52
the sum all the names in sheet 3 also
Excelchat Expert
10/03/2018 - 05:52
Sumifs will work out for this
User
10/03/2018 - 05:52
im sorry if this is difficult for you but im sure you are so good at this that you can come up with some great code
Excelchat Expert
10/03/2018 - 05:53
it would have but it should have been great if you could have shared the sample sheet
User
10/03/2018 - 05:53
so i think if you can write a formula that will work for this example i could adjust or change the file names and locations to suit what i need
User
10/03/2018 - 05:54
sorry the sample sheet is on another computer i don't have access too at this time
User
10/03/2018 - 05:55
i hope time doesn't run out for us
Excelchat Expert
10/03/2018 - 05:56
,seein for john
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.
