Find a character in a substring based on mutiple criteria

[jonastiger]

Hello
I'm asking some help to solve this issue, if you please
I have a data base file and a target file. In target file I need to fill all cells of each table with the matching values from the data base file, based on the table and column names.
In attach, I post a sample file.
The original data base has more than 200 columns,

Thank You all in advance and stay safe and healthy

JT

 

[p45cal]

Look at sheet Folha4 (2) in the attached.
Don't try to enter the formula in one cell (or column of cells) then drag across, it will foul up the structured references to the table. Instead, select the whole mini-grid (eg.K7: P15) edit the formula in the top left cell and commit the formula to the sheet while holding the Ctrl key down. Then you'll have to copy from mini-grid to mini-grid, adjusting the formula a little bit so that it's looking at the correct cells relative to that mini grid (there are only two single cell references to change):
=IFERROR(IF(FIND(K$6,INDEX(Tabela13,MATCH($J7,Tabela13[Level],0), MATCH($J$5,Tabela13[#Headers],0)))>0,"x"),"")

 

[jonastiger]

Thank you very much for your help
It's exactly what I was looking for.

 

[jonastiger]

Hi
Back to this thread, I wonder if it is posssible to create a formula to invert the search well done solved in post #3. I have tried all index-match variations I know but I think there are too many variables.
Please look at 'Folha4(3)' tab.

Thank you very much in advance.
JT

 

[p45cal]

In the attached, sheet Folha4 (3) has the beginnings of a solution but it's difficult from a number of aspects.
Sheet Folha4 (4) has something different.

 

[bosco_yip]

Another option,

1] Create a Data Validation list in J5

2] In K7, formula (only suit for in row 6 table names are in "A","B","C"......) copied across and down :

=IF(ISERR(FIND(K$6,OFFSET($B7,,CODE($J$5)-64))),"","X")

N.B. If your row 6 table names are not in "A","B","C"......, you can replace the formula by :

=IF(ISERR(FIND(K$6,OFFSET($B7,,MATCH($J$5,$C$6:$H$6,0)))),"","X")

Then,

3] Select J5, choose table no. from the drop down list, and the table content will change in accordingly.

 

[Peter Bartholomew]

Using the latest version of Excel 365
= LET( target, XLOOKUP(hdr, headers, data),
present, ISNUMBER(FIND(V, target)),
IF(present, "x", "") )
To revert to standard Excel one could turn target (the matched database column) and present (character found in database record) into Named Formulas. XLOOKUP could be replaced by INDEX/MATCH.


hdr: a column-relative reference to the target file header letter
V: the values 1-6 to search for
headers: table headers excluding the first
data: table body excluding the first column

 

[jonastiger]

Hi
@Peter Bartholomew and @bosco_yip:
Thank you very much for your contribution, there are different but great approaches for the first stage of my problem. But that is solved, thanks and credits to @p45cal (post #2)

@p45cal:
The UDF function works perfect in your example but, when adapt to original file, it always return "none found". In original file, database table is in a another sheet. Does it make difference for Ubound reference? I'm afraid my VBA knowledge is not enough and not familiar with dynamic arrays.

 

[p45cal]

Have you made sure the second argument in the UDF is pointing at any cell in the table you're interrogating, including its sheet ref of course?:

 

[jonastiger]

Hi
Is this right?

Comparing to your print, my SchTbl data adds a bar...
SchTbl cells contain formulas. Is it relevant?

 

[p45cal]

I don't know what those backslash characters are about! Is that character your list separator? What locale are you in? It looks like they're separating columns: where I'd see a comma, you're seeing a backslash. If that's the case it probably doesn't matter.
What are the formulae in those cells?
Perhaps share a workbook with those formula (and the cells where they get values from) so I can try and work out what's going on.
(I presume you're not getting the right results)?

In the file you originally shared, the source table on the left was a proper Excel table (a listObject in vba); the same needs to apply for your real data (I'm talking about the MyTable argument of the UDF). It needs to be any cell in that proper excel table (top right cell is safest).)

 

[Peter Bartholomew]

@p45cal
I think the answer to your question on locale is Portuguese (Brazil). I believe it is normal for languages that use a comma ',' as the decimal point to use semi-colon ';' to separate parameters and backslash '\' for new lines. Thus {1,0;0,1} reads across to become {1;0\0;1}, otherwise MUNIT(2).

 

[jonastiger]

That's right @Peter Bartholomew. I realized that backslash means a new line of the table and yes, here we use semi-colon to separate parameters.

Formulae in SchTbl are simple:
For Col 1 - "=C9", "=C10", ...
For Col 2 - "=IFERROR((IF($E9="n",6,"")&IF($F9="n",5,"")&IF($G9="n",4,"")&IF($H9="n",3,"")&IF($I9="n",2,"")&IF($J9="n",1,""))*1;0)", ...

The attached file in the beginning is very identical to the original. The only difference is that database (proper) table has more than 200 columns+30 rows and it's located in one sheet and the "SchTbl" is in another.
Strange, I replicate all original file over the test file and it works . If add UDF to the original, it always return "none Found". I checked several times.
So, for now, I'm rebuilding over the test file and go on tryng to understand what's wrong.

 

[p45cal]

I'm not at a PC at the moment but I have a hunch that the udf is comparing an empty string with a blank cell and not calling it a match. You could do a quick test and make the SchTbl range only one row deep (and of course 2 columns wide) looking for something you know exists but which is NOT blank.. do you still get none found?

When I get in front of a pc later I'll have a look but I'm travelling internationally today, so although I may find a solution I may not have the internet connectivity to communicate it until tomorrow.

I also note plenty of zeroes in your screen shots, do these exist as zeroes in the source table too? Do you have the setting to display zeroes as blanks switched on?

If you could attach a file with this not working as expected it would allow me to do some detective work - that would be the best.

 

[Peter Bartholomew]

I had a go at reverting the Office 365 solution I postedabove to something that works in standard Excel.
I would be interested to know whether it works (it still appears OK in 365).

 

[p45cal]

I have a hunch that the udf is comparing an empty string with a blank cell and not calling it a match.

That is NOT the problem.

 

[p45cal]

I had a go at reverting the Office 365 solution I postedabove to something that works in standard Excel.
I would be interested to know whether it works (it still appears OK in 365).

For columns J : P, fine.
For the rest:

= _xlfn.LET( _xlpm.target, _xlfn.XLOOKUP(hdr, headers, data),
   _xlpm.present, ISNUMBER(FIND(V, _xlpm.target)),
   IF(_xlpm.present, "x", "") )

 

[Peter Bartholomew]

@p45cal
Thanks for that. The array in columns J : P were all that I worked on; I could have deleted the others.
Alternatively, copy/pasting the first table over the others should give each result in turn since the reference to the letter is relative.

 

[deciog]

p45cal, Peter

Very interesting I liked

Decio

 

[p45cal]

If you could attach a file with this not working as expected it would allow me to do some detective work - that would be the best.

This is what I need now to go further - if it's sensitive and you don't want it in the public domain, send me a Private message/conversation at Chandoo and I'll give you an email address you can send it to - I'll continue with responses in this thread while respecting the privacy required.

As an aside, I said in msg#11

top right cell is safest

which should read lop left cell is safest (cell to choose in a table).

 

[jonastiger]

Hi
Sorry for the late answer.
Here's a replica of the original file. I took off all sensitive data but kept the structure of the 3 sheets relating to the issue
Hope it helps to understand the "none found" error.
One more question - there is a short probability to have two or more equal results. If it happens, would it be possible to return C03, ..., ...?

Thank You for your interest and help
JT

 

[p45cal]

12345 is not the same as 54321, but you're telling me it is?!

 

[jonastiger]

I´m so sorry
Wrong conversion data.
Here's the file with correction

 

[p45cal]

A quick fix is to change the line:

If databody(RowsToCheck(rw), colm) = ValuesToCheck(rw) Then

to:

If CStr(databody(RowsToCheck(rw), colm)) = CStr(ValuesToCheck(rw)) Then

but if you want 12345 to be equivalent 54321, or even 51243 I can do it if you want?

 

[p45cal]

One more question - there is a short probability to have two or more equal results. If it happens, would it be possible to return C03, ..., ...?

I'm fairly sure this has already been addressed and demonstrated in cell O5 of the Folha4 (4) sheet of the file I attached to msg#5