Capture specific data from report

[richmcgill]

I need a way to capture the following pieces of information from the following report. The report evolves from the top format to the format at the bottom but the goal does not change.

Throughout this thirty page daily report is has the 10 digit loan number and the "Last-UPD-USER-ID with the user ID just under this field.

Loan # 0001234567 the LAST-UPD-USR-ID was changed by ARS.
Loan # 0002345678 the LAST-UPD-USR-ID was changed by DCT
Loan # 000345789 the LAST-UPD-USR-ID was changed by FZV

I need the loan number with the USR ID. How do I capture and pair these portions of the report?

All I need is the loan number and the user ID. The user IDs change so I do not have a way to create a table of users.

Can this be accomplished without a MACRO? If not I can try to figure out how to implement a MACRO.

Top portion of the report

Bottom portion of the same report.

 

[jtakw]

Hi,

Not entirely sure I understand your requirements, maybe something like this?

Formula copied down:

Book3.xlsx
	A	B
1	0001234567 504 001 LAST-UPD-USER-ID	0001234567, ARS
2	@WKS ARS ARS
Sheet744

Cell Formulas
Range	Formula
B1:B2	B1	=IF(ISNUMBER(LEFT(TRIM(A1),10)+0),LEFT(TRIM(A1),10)&", "&TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),200,100)),"")

 

[richmcgill]

Works perfectly. Thank you!!!

 

[richmcgill]

One problem has come up. What you showed me above was perfect. The challenge I have is when there is an extra zero on a line.
Looking at the loan number 0002345678 it does not have a zero in front of it.
Looking at 0006482468 it has a zero in front of it. This zero is causing no results to appear.

If you can ignore the zero or figure out how to incorporate it into the loan number that works as well or whatever solution you think might be best?

 

[jtakw]

Works perfectly. Thank you!!!

You're welcome.

One problem has come up. What you showed me above was perfect. The challenge I have is when there is an extra zero on a line.
Looking at the loan number 0002345678 it does not have a zero in front of it.
Looking at 0006482468 it has a zero in front of it. This zero is causing no results to appear.

If you can ignore the zero or figure out how to incorporate it into the loan number that works as well or whatever solution you think might be best?

Try this version:

Book3.xlsx
	A	B
1	0001234567 504 001 LAST-UPD-USER-ID	0001234567, ARS
2	@WKS ARS ARS
3	0 0006482468 504 002 LAST-UPD-USER-ID	0006482468, DCT
4	FIXD DCT DCT
5	0 abc def ghi
6	00 123 456 789
7	0 00 789 abc
Sheet744

Cell Formulas
Range	Formula
B1:B7	B1	=IF(ISNUMBER(IF(MID(TRIM(A1),2,1)=" ",MID(TRIM(A1),3,10),LEFT(TRIM(A1),10))+0),IF(MID(TRIM(A1),2,1)=" ",MID(TRIM(A1),3,10),LEFT(TRIM(A1),10))&", "&TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),200,100)),"")

EDIT: I just noticed that the "Bottom" portion of your report has 11 digit Loan Numbers, is that correct? How should those be handled?

 

[richmcgill]

It is not really an 11 digit loan number but rather a code specifying a condition of the loan and then a space and then the loan number.

The extra digit is not an important number. All I need is the 10 digit loan number.

If it helps place the number (a lot of times blank) in a cell, the loan number in a cell and the user ID in a cell.

Add the number to the loan number and then it can be separated later?

 

[jtakw]

My post #5 formula already took care of that, see the sample in Post #5 Table, Cell A3.

I'm questioning what you have in OP (post #1), "Bottom portion of the same report"
The very first loan number 00003456789 is 11 digits.

 

[richmcgill]

Everything looks great. Thank you so much for your help!

 

[jtakw]

You're welcome, glad it's working for you.