How to Pull line data under a heading name

[TAMMY32]

Hello Excel Forum Group,
I have a file that has a heading number in column B, sheet2, and I would like to look on sheet1 for the same heading then add all the data that in under that heading in columns C, D, G and N. I don't know how to create a macro that would look at a heading (col B) then look on the following row for the equip name (col C) then more 1 row down for the duration (row D) and text (row G). Having the data for the heading on different rows has me perplexed. I would really appreciate any help.

[TAMMY32]

I forgot to add that Headings will already be on sheet 2. I just need to look for the headings that match on sheet1 and add the data in red font to sheet 2

[JeteMc]

This proposed solution adds four 'helper' columns to sheet1 and one to sheet2. All 'helper' columns may be moved and/or hidden for aesthetic purposes.
On sheet1 column A is populated using: =IF(B6="","",RIGHT(B6,LEN(B6)-SEARCH("h",B6)+1))
Column O is populated using: =IF(A6="",O5,A6)
Column P is populated using: =IF(C6="",P5,C6)
Column Q is populated using: =IF(D6="","",INT(D6))
On sheet2 column H is populated using: =IF(A4="",H3,A4)
The total duration column is populated using: =IF(A4="","",IFERROR(INDEX(Sheet1!N$6:N$29,MATCH(A4,Sheet1!A$6:A$29,0)),0))
The equipment, notes, equip duration and date columns are populated using formulas similar to:

Please Login or Register  to view this content.

Let us know if you have any questions.

[TAMMY32]

Hello JeteMc,
Thank you so much for the help! I looked at the file you attached and I believe it really needs a macro instead of formulas. The file on sheet1 will be different everyday. Some days all the headings could have nothing and then the next day it could have several equipments under the heading. Do you have an idea of how that can be done?

[JeteMc]

I can't help with a macro, as I am VBA illiterate. I would suggest that if the headers are not placed as in sheet 2 of the file attached to post #1 you upload another file showing what sheet 2 will look like initially.
Let us know if you have any questions.

[AB33]

Hi Tammy,
I did look in at this thread a while back. I tried to understand the request and output but struggled, so I left for others who might come up with a solution. I have look in it again, I am still none the wiser. I believe part of the problem is that the request is not clear. I suggest you should post realistic data which mimics the actual data and re-post it as new a thread. If a thread received many views, but no solution, it can only mean that the request is not clear. I will keep an eye on your posts.

[jindon]

For the data provided.

Please Login or Register  to view this content.

[TAMMY32]

Hello Jindon and AB33,
I am so sorry I was confusing on the request. It is hard to guess what someone is needing if we don't express it clearly enough. Most of the time I am so cautious to not over explain myself. I will try better this time. I have added hopefully a better example file.

Jindon, thank you so much for the macro you provided. It is real cost to what I was looking for. The only thing is it isn't leaving the headings that have no information on the report. I have added a better file. The first tab is the "1)Starting Report", the second tab is the "2)Form Before Macro" is ran, the third report is "3)Ending Report" how it should look after the macro. I have added a forth tab "4)History 2018". I would like the macro to add the data from "4)Ending Report" to "History 2018". Every time this macro is ran it will add the data on the row after the data that is already on the "History 2018" report.

[jindon]

1) Is the header 1 to 10 always fixed?
2) Don't understand how you add the result to the "History 2018".

[TAMMY32]

Hello Jindon,
Yes the header 1 to 10 is fixed as far as it is the base in which the data from the report (red font) will pull over to the "Form Before Macro". If the Heading has 1 or 10+ equipment lines on the "start report" the data will fall under the heading name. For example: if Heading 1 has 2 equipment's like my example shows it will pull all the text in red font and put it under Heading 1. The next time the report is ran it could have 0 to several equipment's under the heading so the data would push Heading 2 down until all data under heading 1 is done. See example on "Ending Report" how Heading 6 had 4 equipment and then Heading 7 began.

[jindon]

As you said, "Heading 1- 10" is always fixed.

Please Login or Register  to view this content.