Urgently need solution, too tricky for me

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
102
Sorry the first post was messed up...

I have used match function to compare cell A:A to cell E:E, if there's a match cell D displays either true or false. Where there is a match I want the name of that product from cell A to appear on another page.

So

A........ B.....C..........D.................E............F...... G
Product....Qty...£.......Match T/F.......Product....Qty.... £
Tee..........2.....30........T............... Sweat........4..... 100
Shirt........1.....15....... .F................Pants........0..... .0
Sweat......6....140...... .F................ Shirt........7..... 180

So the function I am looking for will search column D for a match (false) and return the product name from cell A so I can then capture and compare the data on another page in the format below

A......... .B..............C...........D......E
Product.....Sales TY....Sales LY.. +-%..+-£
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
See if this works.
Note: Using whole column references can slow things down. If your data won't go past say row 2000, then better to use a range A2:A2000 (assumes header in first row).

This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC). Copy formula down as needed.
Excel Workbook
ABCDEF
1ProductQtyMatch T/F*Product
2Tee230T*
3Shirt115F*Sweat
4Sweat6140F**
Sheet
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
F2 {=IF(COUNTIF($D$2:$D$4,"F")($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))}
I think you are missing a comma after your COUNTIF?
=IF(COUNTIF($D$2:$D$4,"F"),($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))}
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
FDibbins, thanks. Actual it should be a less than < .

Formula in cell F2 above should read:
Code:
IF(COUNTIF($D$2:$D$4,"F") < ROWS($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
I figured there was something missing :)
 

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
102
FDibbins, thanks. Actual it should be a less than < .

Formula in cell F2 above should read:
Code:
IF(COUNTIF($D$2:$D$4,"F") < ROWS($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))
Cheers for the help but that didnt seem to work, here is what I have inputted, have I made an error somewhere?....

{=IF(COUNTIF('Product Data Import'!$D$3:$D$500,"False")<('Product Data Import'!$E$3:E$500),"",INDEX('Product Data Import'!$A$3:$A$500,SMALL(IF('Product Data Import'!$D$3:$D$500="False",ROW('Product Data Import'!$D$3:$D$500)-ROW('Product Data Import'!$D$3)+1),ROWS('Product Data Import'!$E$3:E3))))}

ABCDEFG
2Product NameUnits£Match T/FProduct nameUnits£
3Sweats2100FalseT-Shirts12200
4Shirts...etc
5
6
7

<tbody>
</tbody>


When I enter it it just displays the formula rather than making the calculation. Have I inputted something wrong?

Data source is from tab titled 'Product Data Import'. The above table is the exact layout of mine.
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
Did you type the curly brackets (braces)? If so, don't. This is what you need in the cell:

=IF(COUNTIF('Product Data Import'!$D$3:$D$500,"False")<('Product Data Import'!$E$3:E$500),"",INDEX('Product Data Import'!$A$3:$A$500,SMALL(IF('Product Data Import'!$D$3:$D$500="False",ROW('Product Data Import'!$D$3:$D$500)-ROW('Product Data Import'!$D$3)+1),ROWS('Product Data Import'!$E$3:E3))))

then you click on CTRL+SHIFT+ENTER to confirm it, and this will add the curly brackets for you.

If this isn't it, check if the cell is formatted as text. If it is, delete your formula, format the cell as general and then try again.
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
This means that something is not matching correctly. Use the evaluate formula function on the formulas tab to step through the calculation to see where it is failing.
 

Forum statistics

Threads
1,082,257
Messages
5,364,074
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top