Excel - COLUMN Function Problem - Expert Solution

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
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
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
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
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
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
Excelchat Expert 10/03/2018 - 05:14
sure will do that
User 10/03/2018 - 05:14
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
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
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
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
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
User 10/03/2018 - 05:36
this data is time in hours minutes seconds
Excelchat Expert 10/03/2018 - 05:36
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc