Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula that (1) finds a value in Column A, (2) copies all data in Column A between the cell found in (1) to a new value in Column A. (3) pastes what is copied to a designated range of cells (column) in Column B.
Solved by V. D. in 37 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 18/06/2018 - 08:52
Hi…Welcome to Got It Pro
Excelchat Expert 18/06/2018 - 08:53
Could you show a sample data in this blank sheet?
User 18/06/2018 - 08:53
Excelchat Expert 18/06/2018 - 08:53
User 18/06/2018 - 08:55
let's say I want to copy the cells between A152 to A302 to B1. I know how to do this with a mouse but not with a formula.
User 18/06/2018 - 08:56
I picked A152 to A302 because I wanted all data under the category named on A152 in a new column
Excelchat Expert 18/06/2018 - 08:56
ok...so you will give the input to find the the value, right?
User 18/06/2018 - 08:57
sure. Something along the lines of VLookup but the cell I find would not give data in the adjacent cell but all cells from the cell I find. Does that make sense?
User 18/06/2018 - 08:57
Excelchat Expert 18/06/2018 - 08:58
Yep...let me build the formula
Excelchat Expert 18/06/2018 - 08:59
Will all other values within the text cells be %?
User 18/06/2018 - 08:59
probably, but they could be any form of data really.
Excelchat Expert 18/06/2018 - 09:00
because there has to some way of identifying which kind of data we want from the list
User 18/06/2018 - 09:00
The logic would be thus: "Find "H-HR/FB" in Column A. Copy all data below it until you reach "P-HR/FB
Excelchat Expert 18/06/2018 - 09:01
meaning there must be a distinction between to find value and data to fetch in between
User 18/06/2018 - 09:01
Does the non-Excel logic statement I made make any sense?
Excelchat Expert 18/06/2018 - 09:02
It does
Excelchat Expert 18/06/2018 - 09:02
But there has to be a way to ask excel where to stop
Excelchat Expert 18/06/2018 - 09:03
so kind of how are we differentiating both data types
User 18/06/2018 - 09:03
Right. So the data between the two categories will be numerical (%) data, not text. Yes.
Excelchat Expert 18/06/2018 - 09:03
ok got you
Excelchat Expert 18/06/2018 - 09:04
Let me try to do this with a helper column to identify data
User 18/06/2018 - 09:04
maybe it helps if I share that the whole column is made up of % data with headers separating the groups of % data. I want each header to represent a new column in the spreadsheet.
Excelchat Expert 18/06/2018 - 09:05
ok and is the number of data after each header the same?
User 18/06/2018 - 09:05
no, not at all, unfortunately
Excelchat Expert 18/06/2018 - 09:06
Excelchat Expert 18/06/2018 - 09:06
I thought so
Excelchat Expert 18/06/2018 - 09:06
Ok...give me few minutes...let me figure out a formula
User 18/06/2018 - 09:07
ok . . . sorry to interrupt . . . with my mouse, I just pasted what column B and Column c would look like once a formula is applied.
Excelchat Expert 18/06/2018 - 09:07
User 18/06/2018 - 09:08
don't worry about color highlight, I did that just to discuss. I just need the data itself copied over.
Excelchat Expert 18/06/2018 - 09:08
Excelchat Expert 18/06/2018 - 09:14
Please have a look at the formula in column B and C
User 18/06/2018 - 09:14
Excelchat Expert 18/06/2018 - 09:15
i have extended the formula till the last row you have data in column A, so it will fetch all data irrespective of how many datapoints are there after each header
User 18/06/2018 - 09:16
So, I just copied the raw data to column D to check accuracy of B. Your formula works perfectly! Now I would like to see if I can read your formula for future adaptation. :)
Excelchat Expert 18/06/2018 - 09:17
Sure...this is a index-match lookup function where I added IF statement to stop where we want
User 18/06/2018 - 09:17
Would you mind stating in plain language what the logic is stating in the formula?
User 18/06/2018 - 09:18
So, the IF stops the lookup when what occurs?
User 18/06/2018 - 09:18
rather, it stops the index-match, sorry.
Excelchat Expert 18/06/2018 - 09:18
This looks in column A to find the header text of that particular column. Once it finds it, it starts adding the values from row next to the header found row.
Excelchat Expert 18/06/2018 - 09:19
Now the index-match will continue as long as there is numeric value after the found text.
Excelchat Expert 18/06/2018 - 09:19
The moment it finds a text, it will stop doing lookup and fetching results.
User 18/06/2018 - 09:19
okay...the header stated in B1 is found in Column A with this part: Match(B$1,$A:$A,0) right?
Excelchat Expert 18/06/2018 - 09:20
Another IF to check if the previous cell is blank, if so the cell below it will be blank irrespective of whether it contain a numeric value because that numeric value will now belong to a new header.
Excelchat Expert 18/06/2018 - 09:20
yep, that's right.
User 18/06/2018 - 09:21
What does the +Row($A1) do?
Excelchat Expert 18/06/2018 - 09:21
And ROW($A1) at the end to just increment the row numbers from the found text
User 18/06/2018 - 09:22
I am looking at difference between B Column and C Column. Looks like it is just in the designation of which header I am trying to find (B1 versus C1). Right?
Excelchat Expert 18/06/2018 - 09:23
IFERROR at the beginning and ,"") at the end is an error handler. In case the header that you type is not to be found in Column A, it will return a blank in the first row itself.
Excelchat Expert 18/06/2018 - 09:23
Absolutely, spot on!
User 18/06/2018 - 09:23
Got the IFERROR part, thanks!
User 18/06/2018 - 09:24
This appears to be a simple, elegant formula that is very robust for any list of items in a single column separated by a series of intermittent headers. Really smart. You are an expert!
Excelchat Expert 18/06/2018 - 09:24
Thanks...Glad I was able to assist
Excelchat Expert 18/06/2018 - 09:25
Is there anything else I can help you with this query?
User 18/06/2018 - 09:25
This is my first time on Got It. How do I copy your formula to my actual spreadsheet?
Excelchat Expert 18/06/2018 - 09:25
You can actually download this file as an excel.
Excelchat Expert 18/06/2018 - 09:26
Please Go to File > Download As > Microsoft excel
User 18/06/2018 - 09:26
oops, just figured it out with mouse. But I like the download of the file idea. Will do that. Real quick . . . how long have you been working with Excel and with Got it?
Excelchat Expert 18/06/2018 - 09:26
And you should have this file handy
Excelchat Expert 18/06/2018 - 09:27
I have been with Got it for about 3 months, but been working in excel for quite long....4+ years :)
Excelchat Expert 18/06/2018 - 09:27
Please do give me a good feedback for my service.
User 18/06/2018 - 09:27
Downloaded file successfully. Thanks! Did you have to qualify your expertise to become an advisor? Will definitely give you highest rating. How do I do that?
Excelchat Expert 18/06/2018 - 09:28
When you end this session, it will prompt you to rate my service as well as drop a few lines of your feedback.
Excelchat Expert 18/06/2018 - 09:28
Thanks for your time. Please do come back for any new question. Have a great day ahead!
Excelchat Expert 18/06/2018 - 09:29
And yes we do have a test to qualify to be an expert.
User 18/06/2018 - 09:29
very good. will do. Thanks again for such an elegant, simple equation. Take care. Bye.
Excelchat Expert 18/06/2018 - 09:30
You can actually end this session now. This got auto-extended for another 20 mins :)

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