Extract certain data from 1 sheet and output to another

Hi

I am trying to extract certain data / rows from 1 sheet and output it to another sheet.

for eg this is how 3 of the rows look like

961 emisnet-240772 001DAAEF70B8 InternetGatewayDevice.X_00507F_Status.WAN.3.IPAddress 172.16.2.167
961 emisnet-240772 001DAAEF70B8 InternetGatewayDevice.X_00507F_LTE.LTEStatus.ModemStatus Operational
961 emisnet-240772 001DAAEF70B8 InternetGatewayDevice.X_00507F_LTE.LTEStatus.ModemICCID 8944200012856024447

and another 3 will look like

996 emisnet-237853 001DAA513650 InternetGatewayDevice.X_00507F_Status.WAN.3.IPAddress ---
996 emisnet-237853 001DAA513650 InternetGatewayDevice.X_00507F_LTE.LTEStatus.ModemStatus SIM card not ready
996 emisnet-237853 001DAA513650 InternetGatewayDevice.X_00507F_LTE.LTEStatus.ModemICCID ---

I want to say that anything that starts with a "172.16" copy those 3 rows that are associated with the "emisnet Id" to another sheet. If it does not start with 172.16 then leave it alone.

Any help appreciated.

Thanks

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Do not try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Hi

I have added a sample of the spreaddsheet. Thanks

Hi

Yes that's exactly it. The problem now is tomorrow i will have another report and the day after and so on.
Every day the report has the same information as the previous day but there may be some new entries.
I would like to take the new entries only, and output the same 3 rows onto the new sheet.

Thanks

Hi

Thanks thats what i was trying to do. But it does not seem to work on my actual report. The one i uploaded is just a test one. So i opened my actual report and the sheet is called "Report". so i tried to open another sheet called "Sheet1" and then used your formula, i changed the "sheet1" in your formula to "Report" but that does not work The cell is just empty.

=IFERROR(INDEX(Report!A:A,AGGREGATE(15,6,ROW(Sheet1!A$1:A$99)/(LEFT(Report!$E$1:$E$99,6)="172.16"),INT((ROWS(A$2:A2)+2)/3))+MOD(ROWS(A$2:A2)+2,3)),"")

This did not work. Am i supposed to copy all the in the "Report" sheet to the "Sheet1" first and then use the formula?. Thanks

Hi

I have attached 2 workbooks. 1 has less entries than the other. i would like to have another workbook that has 1 sheet that will show only the 172.16 rows as well as the other 2 row associated with it.Copy of Report (00A).xls
Copy of Report (00C).xls
Thanks

Hi

Thanks for your help but it looks like not all the data is being displayed in the "Sheet1". If you look at the "Report" sheet and do a control F and search for "emisnet-50795" you will see there is an IP of 172.16.5.52, if you then do the same control F on the "Sheet1" it is not there. Is there a problem with the formula?.

i cant upload the workbook as it is 7,000 and the max this forum will upload it 1,000kb. is there another way i can upload it so you can see it.

Thanks

Thanks i see it now

One other problem is that this report is taken every day. so tomorrow there will be another one and on that one there may be more entries or less entries also they may not be in the same order as the one you have been looking at.

Is there a way to add just the 3 values "WAN3 IP, ICCID and status" from tomorrows report into the one i have now "Sheet1"

I will keep the report you have given "Sheet1" but tomorrow i would use a new report and if there are any new entries they would then be imported into "Sheet1" if there are any entries that have been removed they would then disappear from "Sheet1". Is this possible? Thanks

Hi

I have attached today report "Report2" when that is compared to the previous one i have attached there seems to be 3 more entries. so if 1 of those 3 entries
have an IP of 172.16 id like that and the other 2 entries to be added the the previous "Sheet1" from the other day where there is a list of 172.16 entries. Thanks