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!

 

[nhawthorne]

Cell and workbook reference aside, I'm still having problems. I'm pretty sure I know where the issue is though. The money amount that I'm searching for is on a different line in reference to the S/N. Such as:

A B C D
1 S/N ???????
2 sdfjklasdjf;sl
3 -------------
4 NET USD $25,000+
5 -------------

The problem with your formula might be where the search criteria and S/N are in reference to each other. The search needs to find the $25,000 from A4 and return the S/N from A1. Keep in mind the random + sign behind the value and the fact that column A cells are text format.

By the way, thanks for the help so far!!!

N

 

[nhawthorne]

So, after looking over it again, the factors become:

1. Search for the money amount (text string) from workbook 1 in workbook 2.

2. Return the specific cell reference of the cell that contains the money amount.

3. Based on that cell reference, establish a range around the cell of 5 rows on each side. (Cell C5 contains the money amount so the range would become B10:C10)

4. Within the given range search for a cell that contains the text "S/N" and return the value next to it to workbook 1. (Cell C2 = S/N 012345, so the original cell would equal 012345)

Given these factors, what would the formula be? I know that there would have to be a SEARCH, INDEX, and OFFSET function, but don't know in what order to make it work.

N

 

[ExcelChampion]

My formula given will work if you change the references to suit your needs.

If you do not know how to change it to suit your needs then you need to give us:

1. Workbook names.
2. Worksheet names.
3. Cell ranges.

It should end up looking somethig like:

=RIGHT(LEFT(INDEX([Book2]Sheet1!$A$1:$A$35000,MAX(IF(ISNUMBER(FIND(A2,[Book2]Sheet1!$A$1:$A$35000)),ROW($A$1:$A$35000)))-6),12),8)

Confirmed w/Ctrl+Shift+Enter

Where [Book2]Sheet1!$A$1:$A$35000 is the lookup range and A2 is the value to look up.

 

[nhawthorne]

I get what you're saying. I understand the workbook and cell references. The problem is, it's still not returning the correct value. So in an attempt to debug the issue, I've found the mistake in the formula. From what I gather, you're going back -6 rows from the money amount every time. The problem with that is, the S/N isn't always -6 rows up. Nor is the text evenly spaced. So defining the constant # of rows and spaces won't work. I'll try to illustrate, let me know if I'm completely wrong.

EUR COUPON ENTRY PREVIOUSLY DELAYED
S/N 022****** WES VAR 11/08/10 EUR
REC. 10/08/07 DUE 11/08/07 CPN.AMNT EUR ******S
NAR. FUNDS RECEIVED

SCA. EUR 850000
-----------------------------------------------------
NET EUR 34081.27+ 13/08/07
-----------------------------------------------------
DACE REFERENCE IS ********

EUR COUPON ENTRY PREVIOUSLY DELAYED

S/N 022****** WES VAR 11/08/10 EUR

REC. 10/08/07 DUE 11/08/07 CPN.AMNT EUR ******S
NAR. FUNDS RECEIVED

SCA. EUR 850000
-----------------------------------------------------
NET EUR 22222.27+ 13/08/07
-----------------------------------------------------
DACE REFERENCE IS ********

 

[ExcelChampion]

I think a macro would be more efficient for this then.

 

[nhawthorne]

I've tried and can't get a macro to work. Any suggestions?

N