Using Index Match to return multiple results with very messy data.

HI ExcelForum Geniuses.

I am trying to scan through 200000+ (in a single coloumn all messed together) rows of data and return a four digit number every time it find the words "*REQUEST ID*" in the CELL. The Four digit number is in the same cell as the word REQUESt ID.

So the data looks like this:

1COMPANY ID: JET
REQUEST ID: 7991
DATE OF RQ: 13005
TIME OF RQ: 0719
SIGNATURE : ZKZQF
FLIGHT LEG RECORD:
"0101REC AIRCOM-ZK-ZQF/RTE LEPAR WPT YB 39.142S 150.276E.-MEL AKL CMP A04-.NZAA APT/ACFMT PWI/LC 9/ETD 0719/FLT 0133/WX
FL 360/ FPFL1
0R h 0
5 88øænTy “ 0 1< 8q 0QD`þnT¦« ÀQ 9 ÿÿÿðn
T¦¿ DÖ±
TIME_IN_TO_APPN: 071919
TIME_OUTOF_APPN: 071919
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

COMPANY ID: QF
REQUEST ID: 2503
DATE OF RQ: 13022
TIME OF RQ: 0327
SIGNATURE : MFARE41B
FLIGHT LEG RECORD:
#0101REC WSSS-YSSY/RESP T/SUM/ACCRZ 100/RF/RF/RFF/RFFR 100/SHTWD/SHTAD/VFR NODPA/NOETP/ETD 1150/TORWY 02C/AC OQE/ZFW 36
00/360/ FPFL1
0R h 0
5 88øænTy “ 0 1< 8q 0QD`þnT¦« ÀQ 9 ÿÿÿðn
T¦¿ DÖ±
SEGMENT NOT FOUND
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

COMPANY ID: QF
REQUEST ID: 2547
DATE OF RQ: 13022
TIME OF RQ: 0359
SIGNATURE : MFJBY43C
FLIGHT LEG RECORD:
#0101REC YPPH-YSSY/ETOP Y/ACFMT LSP/TAXIN 07/SHDGF 01/TAXOT 11/SHDGT 02/MAXFL 410/MINFL 260/PL 0244/FLTYP D/PER D/SC 4/
ACCLB CI40R1/ACCRZ CI40 CI60 CI80 CI100 CI125 CI150/EODO 1.05/IUSR2 582/TRIP 222/AC OGO/CAPT GOLDING/DIS JAMIE BAYLISS/E
TD 0535/STD 0535/STA 0950/BLOX 0415/POB 198/STLD Y/STPLD 450/ARRFT 00/ARRFF 00/RESP P/SUM/TNKC -99/PSEC N/FLT 582/ATCI N
/SSEC NO/DNC 27621/ 88øænTy “ 0 1< 8q 0QD`þnT¦« ÀQ 9 ÿÿÿðn
T¦¿ DÖ±
TIME_IN_TO_APPN: 035951
TIME_OUTOF_APPN: 035956
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Every time there is a row of XXXXXXXXXXXXX it is a new record. This is just a dump of data I need to analyze in a .txt document. it is all pasted in the same coloumn but each line is on a different row.

The above dump is on Sheet2. In Sheet1 I can pull out the first REQUEST ID by using the formula:

=IF(ISNA(MID(INDEX(Sheet2!$A:$A,MATCH("*REQUEST ID*",Sheet2!$A:$A,0)),26,4)),"",MID(INDEX(Sheet2!$A:$A,MATCH("*REQUEST ID*",Sheet2!$A:$A,0)),26,4))

and I can pull out the first Company by using the formula:

=IF(ISNA(MID(INDEX(Sheet2!$A:$A,MATCH("*COMPANY*",Sheet2!$A:$A,0)),26,4)),"",MID(INDEX(Sheet2!$A:$A,MATCH("*COMPANY*",Sheet2!$A:$A,0)),26,4))

What I need to do is have a formula for this messy data that if I copy the above formulas down it will return the subsequent REQUEST ID on each row as I copy down.

Is this possible?

I have dabbled in VBA a very little bit in the past but not enough to create any solution that could help me.

Please help


p.s. the data is messy with many "artifacts" and btis of font that make no sense. Its an issue with working with such an old system.

I am using Excel 2010 btw.

On a sheet other than sheet 2 use this formula:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000)))),"",INDEX(Sheet2!$A$1:$A$50000,SMALL(IF(ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000)),ROW(Sheet2!$A$1:$A$50000)),ROW(A1))))

Confirmed with Ctrl-Shift-Enter, not just Enter and then dragged down for as many rows as you think you need (it will return blanks once it has listed all of the request IDs)

Whoa, that is amazing and I will spend the next few hours getting my head around this. This will work for those coloumn which have no analytical value except to use as a label i.e. Request ID. However what I am analyzing is the In Time vs Out Time to assess system load. So that would be TIME_IN_TO_APPN: 07:19:19 vs TIME_IN_TO_APPN: 07:19:19 to it took the system one second or less to calculate. I will also look at the date which is DATE of RQ: 13005....(5th day of 2013).

So I will be analyzing the load on the system on a typical week by looking at REQUEST ID 7991 on teh 5th day of 2013 took one second (or less) to calculate.

You method is great for showing labels of the request IDs and the Company feild etc. but for date and time feilds I need to calculate these from the raw text in to an actual value.

For example I am converting the TIME_IN 071919 to 07:19:19 (7 am 19th minute and 19 seconds) by using this formula:

=TIME(IF(ISNA(MID(INDEX(Sheet2!$A:$A,MATCH("*TIME_IN*",Sheet2!$A:$A,0)),26,2)),"",MID(INDEX(Sheet2!$A:$A,MATCH("*TIME_IN*",Sheet2!$A:$A,0)),26,2)),IF(ISNA(MID(INDEX(Sheet2!$A:$A,MATCH("*TIME_IN*",Sheet2!$A:$A,0)),28,2)),"",MID(INDEX(Sheet2!$A:$A,MATCH("*TIME_IN*",Sheet2!$A:$A,0)),28,2)),IF(ISNA(MID(INDEX(Sheet2!$A:$A,MATCH("*TIME_IN*",Sheet2!$A:$A,0)),30,2)),"",MID(INDEX(Sheet2!$A:$A,MATCH("*TIME_IN*",Sheet2!$A:$A,0)),30,2)))

I am trying to copy this down so that each time the MATCH Function sees the word TIME_IN in a Cell it will create and convert the time next to it...then the next row down it will create the next time it sees etc. all the way up to 200,000th result.

Am i expecting too much from excel?

What I'd be tempted to do is put something like this in, say, cell A1 of sheet1:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND({"REQUEST ID","COMPANY","TIME_IN","TIME_OUT"},Sheet2!$A$1:$A$50000)))),"",INDEX(Sheet2!$A$1:$A$5000,SMALL(IF(ISNUMBER(FIND({"REQUEST ID","COMPANY","TIME_IN","TIME_OUT"},Sheet2!$A$1:$A$50000)),ROW(Sheet2!$A$1:$A$50000)),ROW(A1))))

Again confirmed with Ctrl-Shift-Enter

Then, in cell B1:

=IF(A1<>"",TRIM(MID(A1,FIND(":",A1)+1,255)),"")

And in C1:

=IF(ISNUMBER(FIND("TIME",A1)),TIMEVALUE(LEFT(B1,2) & ":" & MID(B1,3,2) & ":" & RIGHT(B1,2)),"")

And copy down for a few thousand rows.

Then you end up with the whole label in column A, just the value in column B and an Excel time value in column C, where appropriate.

Would that be OK?

Should be easy enough.

If you go back to my formula in post #2, but in each column put the value you're searching for. So the formula for column A would be as it is in post #2, for column B it would be:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("COMPANY",Sheet2!$A$1:$A$50000)))),"",INDEX(Sheet2!$A$1:$A$50000,SMALL(IF(ISNUMBER(FIND("COMPANY",Sheet2!$A$1:$A$50000)),ROW(Sheet2!$A$1:$A$50000)),ROW(A1))))

And so on. The bits that say ROW(A1) don't have to be changed, as the row number is only being used as a counter.

Edited to add: I'm calling it a night now (it's 12:45am here in the UK), but if you have any problems let me know and I'll try to help out in the morning.

Thank you so much Andrew... I have added to your reputation. You have been amazing. I will go ahead and have lunch now and I will report back on results after I experiment.. Hopefully all works out
(be back soon)
Kind regards.

With the formula:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("COMPANY",Sheet2!$A$1:$A$189300)))),"",INDEX(Sheet2!$A$1:$A$189300,SMALL(IF(ISNUMBER(FIND("COMPANY",Sheet2!$A$1:$A$189300)),ROW(Sheet2!$A$1:$A$189300)),ROW(A1))))

Is there anyway to display the result of the Cell directly to the right?

I have managed to display the result of the cell directly below by adding a "+1+ here:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("COMPANY",Sheet2!$A$1:$A$189300)))),"",INDEX(Sheet2!$A$1:$A$189300,SMALL(IF(ISNUMBER(FIND("COMPANY",Sheet2!$A$1:$A$189300)),ROW(Sheet2!$A$1:$A$189300)+1),ROW(A1))))

But how do I do it for the value of the cell to the right?

Kind Regards,

That is fantastic and works as required. Thank you Dredwolf. I have one more question to make this perfect.

For my last to coloumns (the TIME_IN and TIME_OUT) coloumns I only have around 10000 results instead of 12777 total entries. The reason for this is than an IN and OUT time on exists when the calculation is successful. So approx 2700 calculations failed. When a calculation fails the text "SEGMENT NOT FOUND" is displayed as can be seen in this section of the raw text:

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

COMPANY ID: QF
REQUEST ID: 2503
DATE OF RQ: 13022
TIME OF RQ: 0327
SIGNATURE : MFARE41B
FLIGHT LEG RECORD:
#0101REC WSSS-YSSY/RESP T/SUM/ACCRZ 100/RF/RF/RFF/RFFR 100/SHTWD/SHTAD/VFR NODPA/NOETP/ETD 1150/TORWY 02C/AC OQE/ZFW 36
00/360/ FPFL1
0R h 0
5 88øænTy “    0 1<   8q 0QD`þnT¦« ÀQ 9 ÿÿÿðn
T¦¿ DÖ±
SEGMENT NOT FOUND
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

. To keep all the coloumns values related to the same calculation across the same row, Is there a way that the following two formulas can be modified to consider "SEGMENT NOT FOUND" and show the value of the cell one to the right of that (which will be blank) and then show that the next in/out time on the following Row?

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("TIME_IN_TO",Sheet3!$A$1:$A$189300)))),"",INDEX(Sheet3!$A$1:$B$189300,SMALL(IF(ISNUMBER(FIND("TIME_IN_TO",Sheet3!$A$1:$A$189300)),ROW(Sheet3!$A$1:$A$189300)),ROW(A1)),2))

and

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("TIME_OUTOF",Sheet3!$A$1:$A$189300)))),"",INDEX(Sheet3!$A$1:$B$189300,SMALL(IF(ISNUMBER(FIND("TIME_OUTOF",Sheet3!$A$1:$A$189300)),ROW(Sheet3!$A$1:$A$189300)),ROW(A1)),2))

Try changing your formula to:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND({"TIME_IN_TO","SEGMENT NOT FOUND"},Sheet3!$A$1:$A$189300)))),"",INDEX(Sheet3!$A$1:$B$189300,SMALL(IF(ISNUMBER(FIND({"TIME_IN_TO","SEGMENT NOT FOUND"},Sheet3!$A$1:$A$189300)),ROW(Sheet3!$A$1:$A$189300)),ROW(A1)),2))

And:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND({"TIME_OUTOF","SEGMENT NOT FOUND"},Sheet3!$A$1:$A$189300)))),"",INDEX(Sheet3!$A$1:$B$189300,SMALL(IF(ISNUMBER(FIND({"TIME_OUTOF","SEGMENT NOT FOUND"},Sheet3!$A$1:$A$189300)),ROW(Sheet3!$A$1:$A$189300)),ROW(A1)),2))

That should keep everything lined up.

Hi, Andrew-R,

I'm trying to learn a bit more of Excel. If it's not too much bother, could you explain how you used the SMALL() function on any formula you provided? What each part is doing, I tried to understand but I'm stuck.

Hope you can help,

Thank you

I'll be happy to try to answer your questions, but would you mind starting it off as a new thread, please, rather than hijacking an on-going thread?

Thanks! and sorry for hijacking this thread. I've created a new one, hope you can help.

http://www.excelforum.com/excel-form...30#post3105230