Advanced lookup or sorting question!

[nhawthorne]

I have a large amount of raw data that I import via a text file into Excel. Here is an example of the data in order for me to illustrate my question.

USD COUPON ENTRY PREVIOUSLY DELAYED
S/N 0243**** dfssdfsdf S 9.37500 07/02/11 USD
REC. 23/07/07 DUE 07/08/07 CPN.AMNT USD 23.437500 Q
NAR. FUNDS RECEIVED

SCA. USD 10860000
-----------------------------------------------------
NET USD 254531.25+ 07/08/07
-----------------------------------------------------
DACE REFERENCE IS *******


On a seperate workbook I have a list of money amounts which I need to find the S/N # for. So if you look above, there is a money amount and a S/N within the raw data. My question is, what can I do to take the money amount from the first workbook and search for that amount in the raw data, then return the S/N number that is within that same area? And do it via a macro or advanced formula so that I can do I many times over?

Keep in mind, even when I delimit the data, it's all in different columns and rows, and never consistently spaced.

Any help would be greatly appreciated!

 

[stanleydgromjr]

nhawthorne,

Welcome to the board.

With the raw data above, what is the value from the 'money amounts' sheet?

And, what is the 'S/N' in the data above?

Have a great day,
Stan

 

[ExcelChampion]

Book1
	A	B	C	D
1	USDCOUPONENTRYPREVIOUSLYDELAYED
2	S/N0243****dfssdfsdfS9.3750007/02/11USD		9.37500	S/N0243****
3	REC.23/07/07DUE07/08/07CPN.AMNTUSD23.437500Q		10.37500	S/N0244****
4	NAR.FUNDSRECEIVED
5
6	SCA.USD10860000
7	-----------------------------------------------------
8	NETUSD254531.25+07/08/07
9	-----------------------------------------------------
10	DACEREFERENCEIS*******
11	USDCOUPONENTRYPREVIOUSLYDELAYED
12	S/N0244****dfssdfsdfS10.3750007/02/11USD
13	REC.23/07/07DUE07/08/07CPN.AMNTUSD23.437500Q
14	NAR.FUNDSRECEIVED
15
16	SCA.USD10860000
17	-----------------------------------------------------
18	NETUSD254531.25+07/08/07
19	-----------------------------------------------------
20	DACEREFERENCEIS*******
Sheet1

/

Formula in D2 (and dragged down):

=LEFT(INDEX($A$1:$A$20,MATCH(9.99999999999999E+307,IF(ISNUMBER(FIND(C2,$A$1:$A$20)),ROW($A$1:$A$20)))),12)

Confirmed w/Ctrl+Shift+Enter

 

[ColdFlo]

hello

Hi, I'm noob can you tell me chonological english translation of that statement so I can know how its said? Learning how things are said helps me visualize. Then I can understand the mind that produced the earliest renditions of this modern syntax. Also, what is 9.9999999999999999E + 307? Are that many nines necessary?

 

[ExcelChampion]

First, let me explain all of those 9s and how it relates to the MATCH function.

9.999999999999999E+307 is the largest number Excel can recognize. The MATCH function above is looking for an approximate match to this big number. So, the closest match to 9.999999999999999E+307 will be assumed to be the last number in the look up range because MATCH assumes the list is sorted in ascending order.

In other words, if I had a list:

2
3
1

the MATCCH function assumes this list is actually sorted in ascending order (even though in reality it is not) and that the last number in the list MUST be the closest match to 9.999999999999999E+307.

With that understood:

Find the value in C2 in the specified range. If the FIND function returns a number then return the row number of where the value in C2 was found. Next, MATCH 9.999999999999999E+307 using the rows returned as the lookup range...this will return the row number that we need. The index function will use that row to return the cell address. Then, return the first 12 characters from the left for the cell address returned.

That's it. If you have Excel 2003 or later you can use the Formula Auditing toolbar to step through the calculation process and see how everything works together...very useful.

As well, after review, this would be more efficient:

=LEFT(INDEX($A$1:$A$20,MAX(IF(ISNUMBER(FIND(C2,$A$1:$A$20)),ROW($A$1:$A$20)))),12)

Array entered.

 

[nhawthorne]

ExcelChampion,

I've tried using your formula, but I cannot seem to come up with the correct answer. The search criteria for the money amount comes from another workbook and the answer needs to be returned to that workbook. That money amount is then found below the S/N line in the raw data. So, below, the number that I need to search for is in BOLD, and the number that I need to return is in BOLD and underlined.

USD COUPON ENTRY PREVIOUSLY DELAYED
S/N 0243**** dfssdfsdf S 9.37500 07/02/11 USD
REC. 23/07/07 DUE 07/08/07 CPN.AMNT USD 23.437500 Q
NAR. FUNDS RECEIVED

SCA. USD 10860000
-----------------------------------------------------
NET USD 254531.25+ 07/08/07
-----------------------------------------------------
DACE REFERENCE IS *******

How could I edit your formula for these specifications?

N

 

[ExcelChampion]

Does this help:

=RIGHT(LEFT(INDEX($A$1:$A$20,MAX(IF(ISNUMBER(FIND(C2,$A$1:$A$20)),ROW($A$1:$A$20)))-6),12),8)

Confirmed w/Ctrl+Shift+Enter

 

[nhawthorne]

Oh, and just in case this matters... There is a plus sign after each money amount in the raw data, and the cells are delimited as text fields. Where as within the other workbook that I am searching from, the money amounts are all formatted as currencies. I would assume this might have an affect on the search, but I could be wrong.

N

 

[nhawthorne]

There are also 35,000 rows of raw data. If that makes any formulaic difference.

N

 

[ExcelChampion]

You need to change the ranges in the formula to suit your needs, but ultimately it should something like:

=RIGHT(LEFT(INDEX($A$1:$A$35000,MAX(IF(ISNUMBER(FIND(C2,$A$1:$A$35000)),ROW($A$1:$A$35000)))-6),12),8)

Array entered.

You will need to change column references as well as workbook references.