Post #4 shows the formula you can use...
Post #4 shows the formula you can use...
Assuming too much and qualifying too much are two faces of the same problem.
@ poer24
=IF(ISERROR(INDEX($A$2:$T$2500,SMALL(IF($C$2:$C$2500=$AA$1,ROW($C$2:$C$2500)),ROW(4:4)),5)),"",INDEX($A$2:$T$2500,SMALL( IF($C$2:$C$2500=$AA$1,ROW($C$2:$C$2500)),ROW(4:4)),5))
is not a formula that I would recommend or post... (There are enough people who start a site on Excel and propagate this kind of nonsense, alas.)
Try rather... In AB1 control+shift+enter, not just enter, and copy down:
=IFERROR(INDEX($E$2:$E$2500,SMALL(IF($C$2:$C$2500=$AA$1,ROW($C$2:$C$2500)-ROW($C$2)+1),ROWS($AB$1:$AB1))),"")
Assuming too much and qualifying too much are two faces of the same problem.
Hi,
I have a similar issue with these posts and I can't seem to figure it out. I have two tables on two separate sheets. Column A on both sheets is the one which matches the other. I want to find all the matches for Sheet 2:A from Sheet 1:A and put Sheet 1:B in Sheet 2:C-X.
For example;
Sheet 1: A Sheet 1: B 13244 BS4W 8952 BCPLSAFCT5L 8952 WMB3FW 8952 EB3FX 8952 MFC10STHF
Sheet 2: A Sheet 2: B Sheet 2: Match 1 Sheet 2: Match 2 Sheet 2: Match 3 Sheet 2: Match 4 13244 bbm1 bs4w 8952 hw10 bcplsafct5l wmb3fx eb3fx mfc10sthf 8952 hwa20 bcplsafct5l wmb3fx eb3fx mfc10sthf 8952 rlx4 bcplsafct5l wmb3fx eb3fx mfc10sthf
I attempted to use the formula you've used in reply #4 but I can't seem to get it to work (I did press Ctrl + Shift + Enter)
@ Nugget_Leodis
Sheet1 (data)
Row\Col A B 1 2 13244BS4W 3 8952BCPLSAFCT5L 4 8952WMB3FW 5 8952EB3FX 6 8952MFC10STHF 7
Sheet2 (processing)
Row\Col A B C D E 1 2 13244BS4W 3 8952BCPLSAFCT5L WMB3FW EB3FX MFC10STHF 4
In B2 control+shift+enter, not just enter, copy across, and down:
=IFERROR(INDEX(Sheet1!$B$2:$B$6,SMALL(IF(Sheet1!$A$2:$A$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B$2:B2))),"")
Assuming too much and qualifying too much are two faces of the same problem.
Hi Aladin,
Could you elaborate the below functions for me...? As I am not getting couple of ranges you referred to MATCH fuction, also before MATCH function threre is IF($B$2:$B$14<>"" statement which I am not getting as well. thanks in advance.
=IFERROR(INDEX($B$2:$B$14,SMALL(IF(FREQUENCY(IF($A$2:$A$14=$D2,
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1),
COLUMNS($E2:E2))),"")
Regards,
Kamran Mumtaz
PAKISTAN.
--------------------------------------------------------------------------------------------------------------------------------------------------------
Playing with excel - 2007
Want to post a small screen shot? Try this Excel jeanie,
Try searching for your answer first, see how
@ proficient
This refers to the formula which is forwarded in post #26 .
The bit:
FREQUENCY(IF($A$2:$A$14=$D2,of the formula creates maps the occurrences of the non-blank cells in B2:B14 to the bin-array ROW(B2:B14)-ROW(B2)+1, which correspond to the occurrences of D2 in A2:A14.
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1)
A. $B$2:$B$14<>"" means: skip blanks/empty cells.
B. MATCH($B$2:$B$14,$B$2:$B$14,0) matches the range interest against itself, leading to numbers showing matches.
C. FREQUENCY works with numbers (data_array) whose occurrences it classifies into bins_array.
_____________________________
Intermezzo:
item match item set against itself (data_array) bins_array count of B results of count B JAD 1 1 from 0 to >= 1 2 = 2 times 1 VAD 2 2 from 2 to >= 2 1 = 1 times 2 JAD 1 3 from 3 to >= 3 0 = 0 times 3 KAD 4 4 from 4 to >= 4 1 = 1 times 4 rest 0 = 0 times anything beyond 4 How many results are above 0? 3 This count is precisely the number of unique items JAD, VAD, and KAD.
_____________________________
D. The IF bit, i.e.
IF(FREQUENCY(IF($A$2:$A$14=$D2,of the formula returns the rows of non-zero results.
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1)
>>
IF(non-zero results of frequency, ROW($B$2:$B$14)-ROW($B$2)+1)
E. Feeding the rows from [D] to INDEX looking at $B$2:$B$14 one by one by means of SMALL, we get the list of unique items from $B$2:$B$14.
See for more:
https://www.mrexcel.com/forum/excel-...ml#post3156949Hope this helps.
Last edited by Aladin Akyurek; Feb 14th, 2018 at 01:44 PM. Reason: formatting
Assuming too much and qualifying too much are two faces of the same problem.
Regards,
Kamran Mumtaz
PAKISTAN.
--------------------------------------------------------------------------------------------------------------------------------------------------------
Playing with excel - 2007
Want to post a small screen shot? Try this Excel jeanie,
Try searching for your answer first, see how
Like this thread? Share it with others