Help needed - Extracting rows from excel sheet based on a value

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
i have the following excel sheet which has data that is coming to me in the format as shown in "input" tab. I would like to read the sheet and output the data in the format as shown in "output" tab.

new to macros...If there is a macro that can do this, that would be great.

Thanks for your help.
 

Attachments

  • test1.xlsx
    12.6 KB · Views: 20

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
not sure what you did...can you explain?

View attachment 1436 No macros needed. See attached.

not sure what you did...can you explain? It looks like the same file that I provided in my question.
please note that Input tab contains the format of the records (sample given for 4)....But I have 1000's of those that I need to read thru and extract the rows and put them in columns....
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Open the file, click on any cell in colmuns A, C, D, E in column B that has something in it, and you'll see that there's a formula in it that pulls data from the INPUT sheet based on the value in column B.
 

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
Open the file, click on any cell in colmuns A, C, D, E in column B that has something in it, and you'll see that there's a formula in it that pulls data from the INPUT sheet based on the value in column B.

Thanks, Jeffery. It works, if I know the Column B value.

What I am looking is, scan the input sheet and extract the required row values ...format them in the output format. The control break for each set is, the row values in A1, A7, A13 and A19.

Thanks
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
View attachment Test1 (1).xlsx

Okay, see the attached. Note that I've applied a custom number format to column B so that zeros don't display in the event that we've reached the bottom of the list.

Note that this relies on you having the exact structure you currently have in the Input tab. Insert extra rows, and this will break.
 

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
View attachment 1441

Okay, see the attached. Note that I've applied a custom number format to column B so that zeros don't display in the event that we've reached the bottom of the list.

Note that this relies on you having the exact structure you currently have in the Input tab. Insert extra rows, and this will break.

Thanks. Let me test this...

If I have extra rows and format is same , should I adjust the formula? Currently I have 6 rows per set. If I have 10 rows, what precautions should I take?
 
Top