Loop through column a tricky one

[mohamadmh]

Hi all,

Struggling to find solution for this one. I need to loop through rows in column A and populate columns B1:Nth1 with text = field & # from row. Then populate values in column B2:Nth# from values in based on values in sting in column A. See attached example. Items in yellow are derived from data in column A. Any help would greatly appreciated.

 

[Deniall]

There does not seem to be any example attached.
[The ROW() function returns the row number of the argument, or the number of the current row if it has no argument.]

 

[mohamadmh]

Hi Deniall,

Apologies, File attched

 

[SkipVought]

You state that in all but one row, the value in field 4 "corresponds" or not to a value "from the XYZ record MIC ABCD"

The results in fields 2,8 & 9 are either "empty", BLANK (ie empty cell) or a value from field 9.

We have no visibility to the values "from the XYZ record MIC ABCD."

For the first row, would this way of stating what is/is not happening is...
field 3<> field 4: field 4 = "empty"
field 3 = field 8 = field 9: no op


The information you are providing is nebulous. As the saying goes, "What happens in vagueness stays in vagueness."

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

For me the logic is:
1) after 'The value for this' find consecutive 'field x',
2) find all words between 'does not correspond to value' and 'received from', in the example they are either of 'YYYY' form or 'empty',
3) in B:E columns place words found in step (2) according to headers found in 1.

It's just my guess, assuming that it is so and there are no other issues (as missing or more headers), I would analyse the sentence (VBA):
1) remove commas,
2) use Split function to convert sentence to array of words,
3) find all 'field' (with LCase) followed by number, say, n instances,
4) find n words after 'correspond' + 'to' + 'value' (or shorter list if unique), test,
5) put found words in columns, according to mapping numbers found in (3).


combo

 

[mohamadmh]

Hi Skip and Combo,

Thank you both for your replies. Combo you are correct in your logic. I am not that technically savvy yet to use array of words. Would need your help with the VBA code. Any suggestions would be greatly appreciated.

Skip - for example: The value for this field 3, field 8, Field 9, does not correspond to value XYZA, empty, empty, received received from the XYZ record MIC ABCD


Field 3 = ZYZA
Field 8 = empty
Field 9 = empty

 

[mohamadmh]

typo - Field 3 = XYZA

 

[combo]

What is your knowledge of VBA? People here can help, but don't expect ready to use solution.

For the beginning:
1) remove commas from MySentence string:
[tt]MySentence_1 = Replace(MySentence, ",", "")[/tt]
2) convert string to variant array of words, using single " " as delimiter:
[tt]Dim MySentenceWords as Variant
MySentenceWords = Split (MySentence_1, " ")[/tt]

combo

 

[mohamadmh]

Thnks for your input combo. Def need help with the code.

 

[mohamadmh]

Hi All,

Stuck on this issue. Still learning VBA. Would really appreciate any help.

 

[Andrzejek]

any (VBA) help" with what? You have to be very specific

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mohamadmh]

Hi all,

Thank you for your feedback. Apologies I was not very specific. I need help with writing the VBA code. Thank you again for your input and feedback.

 

[Andrzejek]

That was very specific...
You may start your 'writing the VBA code' by doing this, and you will find one of the places here

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson