Set reference, find it, find where text matches, bring across value

[gwsampso]

Im trying to bring across the value of a cell after it finds the set reference and matches some text

Thanks to the help of this forum we were able to create a macro to bring across my desired cells using .SpecialCells

Example: email.xlsm

Its working beautifully but now i’m trying to use the “reference numbers” eg. 01806220120914/0195

Then search/find for the occurrence where the “reference numbers” - 01806220120914/0195 equals that set reference number 01806220120914/0195 in column “C” of my “Bulked Letters” sheet and where the first 7 letters of "AC" equal "PAYDATE".

Then bring across this value into my other workbook.

The move to the next reference number...

Any ideas welcome please

end result.JPG
letters bulked.jpg

[rylo]

Hi

If there is only going to be one result per reference number then try

=MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$834=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$834,7)="PAYDATE"),ROW('Letters - Bulked'!$J$2:$J$834))),22,11)

rylo

[gwsampso]

rylo you've done it again mate thats great gives me something to work with, but do you or anyone know how to make this happen via vba so theres no formula on the sheet?

Reason being, i get the "letters - bulked" spreadsheet emailed seperatly daily, bring across my selected information into my master then delete the letters bulked.

So if there were a forumla it would break the next day, when a new bunch of information and "reference numbers" come through.

Thanks again

[rylo]

Hi

Please Login or Register  to view this content.

Something I did notice is that you don't have a valid result in L9 in the example file as there isn't a matching entry in the source data. Is this just a quirk of the example, or is this likely to really happen?

rylo

[gwsampso]

it will break because there are 3 options

PAYDATE
EFFECTIVE DATE
OFFERS CLOSE

but im hoping.... im good enough to be able to modify whatever the forum throws at me.

i hate loading people up with too many scenarios

[gwsampso]

hmm im not seeing any action with your code

**Edit**

it is bringing across the data to column L in the "Letters - Bulked"....

[rylo]

Hi

Where are you putting the macro? General module, or in a sheet module? This is meant to sit in a general module.

rylo

[gwsampso]

you've done it again! much appreciated

[gwsampso]

oh no! I thought i was good enough to convert this into an IF Statement.... any ideas why my changes don't effect the outcome at all?

Please Login or Register  to view this content.

[rylo]

Hi

Which sheet are you on when you start the run? Because after the first iteration, you will be on sheet NEW and I don't know if the IF statement wil come back true or not.

rylo

[gwsampso]

i've been starting the run on the "New" which was the changed name for the "Current"

changed everything back but now nothing to a senario i hoped would work but still nothing

Please Login or Register  to view this content.

[rylo]

Hi

new example file time, for the structure you really want to use, and advise where / how you want this to action.

rylo

[gwsampso]

morning, new week hopefully a good week!

updated example attached with notes and final outcome

thanks rylo

update example.xlsm

[rylo]

Hi

Here goes

Please Login or Register  to view this content.

rylo

[gwsampso]

so so solid

i was well off!

Thanks again rylo