Formulas not working as intended - not sure why

[kmclean.wesco]

Hi all,

I have received previous help with the formulas that exist in this file, and it seemed to have worked initially, though it appears there's something not working as intended and I am not sure why.

Formula in Question on SALES HISTORY & SPA ANALYSIS TAB:

Cell A3 - =UNIQUE(FILTER('Custom List Data Entry'!C3:P5000,ISNUMBER(MATCH('Custom List Data Entry'!C3:C5000,'Custom List Data Entry'!A3:A5000,0))))

This is supposed to return the data from the "Custom List Data Entry" table range C3:P5000 if the values in column C3 and A3 match.

While it appears to do so for some lines, it doesn't appear to be catching everything that should match. Example of a match it's catching is on the Custom List Data Entry tab, C31 matches with A143 and pulls the data from referenced table (C3:P5000) and appears on the SALES HISTORY & SPA ANALYSIS TAB on row 3 (as intended). An example of where it doesn't appear to be working but appears to meet the formulas logic is on the Custom List Data Entry tab, C187 matches with A22 but it doesn't appear on any row on the SALES HISTORY & SPA ANALYSIS TAB.


The other formula that's on SALES HISTORY & SPA ANALYSIS TAB is in cell O3 =LET(f,FILTER('Custom List Data Entry'!A3:B3300,NOT(ISNUMBER(MATCH('Custom List Data Entry'!A3:A3300,'Custom List Data Entry'!C3:C3300,0)))),FILTER(f,(INDEX(f,,1)<>" ")*(INDEX(f,,1)<>0)))

This formulas intention is to return in columns O & P the exact data in columns A & B on the 'Custom List Data Entry' tab when there is NO match between columns A & C on 'Custom List Data Entry' tab. In my example where the previous formula appears to not be working (should be finding a match on 'Custom List Data Entry tab in C187 and A22') these lines data is being returned as not finding a match in columns A & C on 'Custom List Data Entry' tab.

Maybe these formulas are at odds with each other? Maybe it's a formatting issue? HELP! Thank you.

[AliGW]

on the Custom List Data Entry tab, C187 matches with A22

No, it doesn't:

A22: 78621056297
C187: 61601310702

I can't see anything wrong here, and as that's the only 'error' you've pointed out, there's nothing more that I can do at this juncture.

It would be helpful if you would at least highlight where you think there are other issues in the workbook.

[kmclean.wesco]

Thanks for the response.

I highlighted cell A22 = 61601310702 and C187 = 61601310702. Am I missing something here?

I highlighted the above cells in yellow. I also highlighted an example of where I believe the formula is working in green for cross reference. I added notes with the yellow or green highlights for reference.  

[AliGW]

From the workbook you attached to post #1:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A
22	78621056297

Sheet: SALES HISTORY & SPA ANALYSIS

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C
187	61601310702

Sheet: Custom List Data Entry

[AliGW]

Really not clear what you mean as I can see it (highlighted) in the list.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B
3	01899758175	ARL LV1 LOW-VOLT-MTG-BRACKET
4	06739600331	ABB 331P WIRECONNXTPNONWING ORGNO18
5	06739600331	MAR 331P 18-14-ORG-WIRE-CONN
6	06739600333	MAR 333P 18-10-YEL-WIRE-CONN
7	06739601331	MAR 331M WIRE-CONN
8	61601310700	ABB CPC075 1-PIECE CBL/PIPE CLP 3/4
9	61601310702	ABB CPC100 1-PIECE CBL/PIPE CLP 1IN
10	61601311153	S-S B1400-10PG GALV-CHANNEL
11	61601311163	ABB B1400HS-10PG HS CHNL 14GA 1-5/8
12	61601311163	S-S B1400HS-10PG GALV-SLOTCHNL
13	62253843820	ABB CI820-D REWORK SUPP BRACKET MAX
14	62253843820	T-B CI820-D SW-BOX-SUPPORT-BRACKET
15	62258805183	EAT 1HD362 3P-60A-SFTY-SW
16	62258805187	CSE 1HD363 3P-100A-SFTY-SW
17	62258805190	CSE 1HD364 3P-200A-SFTY-SW
18	62258805190	EAT 1HD364 3P-200A-SFTY-SW
19	62398036067	HMN CSKO12124 12X12X4IN-SCR-CVR
20	78331021481	GRN 430 1PLY-6500FT-PULLLINE-B
21	78599190781	STL IT100SC 0-ANTI-SHORT-BAG/35
22	78621024803	T&B H104-3/8X10EGC HANGER-ROD
23	78621056297	T&B A1200S10PGC 1-5/8X1-5/8-SLOT-CH
24
25	WHY ISN'T 61601310702 BE APPEARING ABOVE LIKE 01899758175 IS?

Sheet: SALES HISTORY & SPA ANALYSIS

[kmclean.wesco]

Here's what I'm seeing from the post #1 download? row 9 has 61601311153?

Attachment 860514

[kmclean.wesco]

This screenshot format you did here would be easier to use to communicate. Do you mind sharing how you posted this? I'll go through all the advanced features now to see if I can figure it out.

[kmclean.wesco]

00
You know what.....I think this happened to me earlier today and I couldn't figure out what happened. Row 8 that shows 61601310700 actually wasn't there either, but after doing some "clicking around" (AKA I have no idea what triggered this) it just appeared in the list shown here. I still don't know why it's showing all the way to the right of the column.

[kmclean.wesco]

Further to post 9, here's another example of something that did not make it into the array on 'SALES HISTORY & SPA..". On the 'Custom List Data Entry' A25 showing 61601310708 and C191 showing 61601310708. Not in the above.

[AliGW]

That's not what is showing in the workbook attached to post #3.

[AliGW]

This screenshot format you did here would be easier to use to communicate. Do you mind sharing how you posted this?

No - for this we need a workbook. Just cut down the sample workbook to a reasonable amount of data (10-20 rows ONLY) that show the problem. I am not going to trawl through 100s of rows of data as it currently stands - sorry.

[kmclean.wesco]

I understand and I appreciate you sticking around with me. I am still trying to learn how to make this easy for the pros such as yourself. Sometimes I only have words! ... LOL. I will send something shortly.

[kmclean.wesco]

Ok, here's the worksheets. The lines highlighted in yellow on the 'Custom Data Entry List' A & C are matches that are not returning into the table array on 'SALES HISTORY & SPA ANALYSIS'

I noticed that there are some duplicate values in column C on 'Custom Data Entry List' (EG. 62253823406 in C302 & C303). I would like to return both of these matches to the table array 'SALES HISTORY & SPA ANALYSIS'.

[AliGW]

I still don't know why it's showing all the way to the right of the column.

It isn't - that's the number in the row above it.

Anything left-aligned is TEXT, anything right-aligned is a real NUMBER.

[AliGW]

I am waiting for a sensibly cut-down workbook as requested in post #10.

Administrative Note re. Forum Guideline #2:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[AliGW]

OK - thanks. Simplify it as much as you can without losing what you are trying to demonstrate.

I have a hunch that this is related to numbers not being numbers, but text (or vice versa), as I mentioned in post #11.

[AliGW]

This is just the same sheet with rows hidden - that's not what I asked for.

I can tell you that this:

=A3=C213

returns FALSE for the first SIX rows at least (I have not tested further).

This means that those values do NOT match, even though you think they do. Column A contaiuns REAL NUMBERS and column C contains TEXT.

You could try this:

=UNIQUE(FILTER('Custom List Data Entry'!C3:P5000,ISNUMBER(MATCH(--'Custom List Data Entry'!C3:C5000,'Custom List Data Entry'!A3:A5000,0))))

UNTESTED as I don't have time at the moment.

[kmclean.wesco]

Sorry, I'm not fully understanding what you need from me. Thanks for the tip on the FALSE return, I will start there to try and figure this out. The formula you gave me doesn't seem to work so far but user error is very possible. I am having issues finding a consistent format on my lists... they don't want to all change to one format type. Some seem to either stay a NUMBER or TEXT or GENERAL.

[AliGW]

Further - when both columns are set to GENERAL formatting, you can clearly see the difference in alignment (see post #11 for explanation).

[AliGW]

You can't CHANGE a column's underlying format using those format options. They are just a way of viewing the data, not formatting it. The GENERAL option merely shows that the data in A is NOT in the same format as that in C, therefore those rows do NOT match.

What I want from you is this: a cut-down workbook with just a few rows of data - some rows that work and some that don't. I do NOT want ALL of the source data there.

Give me 20 rows of results and 30 rows from the source data (rows that are meant to appear - make sure there are some that do and some that don't). Put these and your formula into a new workbook. Then I'll look again.

[kmclean.wesco]

Ok... I hope I did this right!

Sales History Data Entry broken to two lists. Green is what is returning to the 'SALES HISTORY & SPA ANALYSIS' array and Orange is what is not but should match. On the 'Sales History Data Entry' page, I ran the TRUE/FALSE test in columns K & L and it is checking out.

Did I do this correctly? Fingers crossed!

[AliGW]

It's what I said - the items that are not matching don't match. All values in C are TEXT, but those in A are a mixture of text and numbers. Those that are numbers don't match.

This should resolve it:

=UNIQUE(FILTER('Custom List Data Entry'!C2:P5001,ISNUMBER(MATCH('Custom List Data Entry'!C2:C5001,VALUETOTEXT('Custom List Data Entry'!A2:A1721),0))))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

Seen, but no reply ...

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[kmclean.wesco]

Thanks Ali! This solves this issue! Now, there's one more thing that has happened and I suspect it's going to be similar. I am not sure if I should start a new thread though? I will post it here but if this is soething that should go on it's own thread please let me know.

Formula '=LET(f,FILTER('Custom List Data Entry'!A3:B3300,NOT(ISNUMBER(MATCH('Custom List Data Entry'!A3:A3300,'Custom List Data Entry'!C3:C3300,0)))),FILTER(f,(INDEX(f,,1)<>" ")*(INDEX(f,,1)<>0)))" in cell O3 on ''SALES HISTORY & SPA ANALYSES was originally intended to filter and return columns A&B on 'Custom List Data Entry' where column A did not match with any values in column C.
Instead, O3 (the formula) seems to be returning all values in column A&B and not filtering.

EG. Cells O2:P2 on ''SALES HISTORY & SPA ANALYSES' are returning "62001330000" & "EMT EMT-CONDUIT-1/2 1/2 16MM" even though on sheet 'Custom List Data Entry' cell A3 ("62001330000") SHOULD match BUT actually DOESN'T based on what you taught me... EG. =A3=C213 = FALSE).

Do we have to add the same VALUETOTEXT somewhere in this formula? I tried it in a few spots within the formula no avail...

[AliGW]

So you need to make the same adjustment to that formula as I gave above for the first one. I’m just off to bed - have a go and I’ll look in and see how you’ve got in tomorrow.

[AliGW]

Try this:

=LET(f,FILTER('Custom List Data Entry'!A3:B3300,NOT(ISNUMBER(MATCH(VALUETOTEXT('Custom List Data Entry'!A3:A3300),'Custom List Data Entry'!C3:C3300,0)))),FILTER(f,(INDEX(f,,1)<>" ")*(INDEX(f,,1)<>0)))

Maybe you forgot the final bracket for the VALUETOTEXT function?

Hopefully that's it now.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[kmclean.wesco]

I hadn't had much sleep the last two days so I slept in on this! Thanks for solving this one yet again. I am taking a beginner course on excel right now and were just getting into "nesting" and how that works.