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
sure
Excelchat Expert
18/06/2018 - 08:53
Thanks!
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
"down"
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
ok
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
sure
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
ok
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.
