Referencing rows on an export that have specific data

[oneyejack77]

I have a summary sheet that will reference an export sheet. I want to check the export sheet column G for the word "Demand". If it is there I want to display the corresponding value from the other cells in it's row.

I also need these to be listed in order with no blanks.

Export sheet

Data I need.jpg

Summary sheet

Data Summerized.jpg

[ashishkhurana93]

Can you attach the sheet so that i can do it accordingly within the sheet itself

[oneyejack77]

Unfortunately no, there is some CPNI on it, that I can not share. If I were able to only move and populate the cells in column G with the corresponding node name in column A I could Index Match the rest.

[JohnTopley]

Try

on "Summary Sheet"

=IFERROR(INDEX('Export Sheet'!$A$2:$A$1000,SMALL(IF($G$2:$G$1000="Demand",ROW('Export Sheet'!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Change Sheet/range as required

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

This will return "Node" from column A

[oneyejack77]

Sorry, I can't seem to get that solution working. I changed the cell references and made sure the array was active, but I only get blanks (actually #N/A), due to the IFERROR.

Any chance you could spell it out so I could follow the logic, I'm a newbie to formulas in Excel. I'm not sure what Row and Small functions are doing for me.

Here is what I used:

=IFERROR(INDEX('Demand Ticket Setup'!$A$2:$A$4000,SMALL(IF($G$5:$G$4000="Demand",ROW('Demand Ticket Setup'!$A$2:$A$4000)-ROW($A$5)+1,""),ROWS($A$5:A5))),"")

[JohnTopley]

Try

=IFERROR(INDEX('Demand Ticket Setup'!$A$2:$A$4000,SMALL(IF($G$2:$G$4000="Demand",ROW('Demand Ticket Setup'!$A$2:$A$4000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

I you still have problems, post the subset of the file that is in your image: A1:H40 as it (apparently) does NOT contain confidential information.

[FlameRetired]

Another way. It is the same idea as JohnTopley's and is a non array solution. I've supplied my own stripped down file.

It uses a helper column to assign indexing numbers with this formula.

Please Login or Register  to view this content.

Then this formula in the summary.

Please Login or Register  to view this content.

Can you adapt this?