Oct 16 2020 09:23 AM - edited Oct 16 2020 09:25 AM
I am trying to figure out how to use the Lookup formulas in Excel to get what I need.
I have a 2x100 table of values. The left column, "A", has a series of unique numbers. The right column. "B" has several repeated values, as many as 10 of each value, so not unique.
EX: Source Table
A B
111 Squeeze
112 SacBnt
113 Sneaky
114 PulBak
115 Bnt&Rn
121 Slap
122 FakTak
123 Take
124 Steal
125 Ht&Rn
131 Delay
132 Cancel
133 Repeat
134 Squeeze
135 SacBnt
141 Sneaky
142 PulBak
143 Bnt&Rn
144 Slap
145 FakTak
151 Take
152 Steal
153 Ht&Rn
154 Delay
155 Cancel
211 Repeat
212 Squeeze
213 SacBnt
214 Sneaky
215 PulBak
221 Bnt&Rn
222 Slap
223 FakTak
224 Take
225 Steal
231 Ht&Rn
232 Delay
233 Cancel
234 Repeat
235 Squeeze
241 SacBnt
242 Sneaky
243 PulBak
244 Bnt&Rn
245 Slap
251 FakTak
252 Take
253 Steal
254 Ht&Rn
255 Delay
311 DrgBnt
312 Repeat
313 Squeeze
314 SacBnt
315 Sneaky
321 PulBak
322 Bnt&Rn
323 Slap
324 FakTak
325 Take
331 Steal
332 Ht&Rn
333 Delay
334 DrgBnt
335 Repeat
341 Squeeze
342 SacBnt
343 Sneaky
344 PulBak
345 Bnt&Rn
351 Slap
352 FakTak
353 Take
354 Steal
355 Ht&Rn
411 Delay
412 DrgBnt
413 Repeat
414 Squeeze
415 SacBnt
421 Sneaky
422 PulBak
423 Bnt&Rn
424 Slap
425 FakTak
431 Take
432 Steal
433 Ht&Rn
434 Delay
435 DrgBnt
441 Repeat
442 Squeeze
443 SacBnt
444 Sneaky
445 PulBak
451 Bnt&Rn
452 Slap
453 FakTak
454 Take
455 Steal
In a separate table I need to look for the lowest "A" value of each "B" value, then the next lowest, etc... So in the top row of the following table, I need the lowest "A" value of "Take" from the first table, which is 123, then the next lowest is 151, then 224, etc...
EX: I need to end up with this table after referencing the source table.
Take 123 151 224 252 325 353 431 454
FakTak 122 145 223 251 324 352 425 453
Bnt&Rn 115 143 221 244 322 345 423 451
DrgBnt 311 334 412 435
SacBnt 112 135 213 241 314 342 415 443
Sneaky 113 141 214 242 315 343 421 444
Squeeze 111 134 212 235 313 341 414 442
PulBak 114 142 215 243 321 344 422 445
Slap 121 144 222 245 323 351 424 452
Ht&Rn 125 153 231 254 332 355 433
Steal 124 152 225 253 331 354 432 455
Delay 131 154 232 255 333 411 434
Cancel 132 155 233
Repeat 133 211 234 312 335 413 441
I've attached a file with the source and output tables.
Oct 16 2020 09:52 AM
L1
=UNIQUE(Source!B1:B100)
M1
=TRANSPOSE(SORT(FILTER(Source!$A$1:$A$100,(Source!$B$1:$B$100=L1))))
Oct 16 2020 09:56 AM
@DonMirabella You can use the following 2 formulas:
to create the unique list from B:
=UNIQUE(B:B)
Then to create the list you want:
=INDEX(TRANSPOSE(FILTER(A:B,B:B=E1)),1)
where the original list is in columns A and B and the new lists are in E and F. You then copy that formula down
Oct 16 2020 10:03 AM
@mtarler We don't have Excel 365 so no Unique function available.
Oct 16 2020 12:01 PM
@DonMirabella If you are on Excel 2019 you can use TEXTJOIN in a three step solution as shown in the attached workbook. First create a pivot table, then TEXTJOIN, and then text-to-columns. Not very elegant, but fairly easy and workable if this is something you do once or only every now and then.
Otherwise, use PowerQuery.
Query the table in the two columns, add an index column, pivot the index with column A as the value (without aggregation). Then merge all the columns except the first one, using a space as delimiter. Trim the lot and split the column by space delimiter to get the table you desire. Load back to Excel.
Oct 16 2020 12:27 PM
As variant you may create PivotTable on the range with adding data to data model and create the measure to add the numbers as
Numbes:=CONCATENATEX(Range,Range[Number],", ")
Result is
Oct 16 2020 12:51 PM
@Sergei Baklan Thank you Sergei, this looks like the way to go for me. However, when I try to setup my own pivot table in my spreadsheet, the "Add this data to the Data Model" check box is greyed out.
I don't see a reason why nor do I see how to change that.
Don
Oct 16 2020 12:52 PM
One more variant
In G4
=IFERROR(
INDEX($B$2:$B$101,
AGGREGATE(15,6,1/(COUNTIF($G$3:G3,$B$2:$B$101)=0)*(ROW($B$2:$B$101)-ROW($B$1)),
1)
),"")
and drag it down till empty cell appears.
In H4
=TEXTJOIN(", ",1,IF($B$2:$B$101=$G4,$A$2:$A$101,""))
and also drag down
Oct 16 2020 01:00 PM
Don, not sure what it could be with data model. Try formula solution, perhaps it'll be easier and not necessary to refresh data even if PivotTable works.
Oct 16 2020 01:10 PM
@Sergei Baklan I found the solution in a few different posts. Had to save as xlsm then close and reopen.
Now, I'm not able to recreate your pivot table in my sheet. I don't see the function option in Range field selection window.
I'll work on this over the weekend, seems like it'll take some head scratching.
Oct 16 2020 01:31 PM
Oct 16 2020 02:09 PM - edited Oct 16 2020 02:11 PM
@DonMirabella another option if you need the numbers in separate cells.
use this equation (which is probably similar or same as previously posted to find the unique names):
Note: I defined dRange as column B on the Source Tab
= IFERROR(OFFSET(dRange,AGGREGATE(15,7, ROW(dRange)/(--(COUNTIF(OFFSET(dRange,ROW(dRange)-1,0,ROWS(dRange),1),dRange)=1)), ROW())-1,0),"")
and then paste this and drag down and to the right as far as needed:
= IFERROR(OFFSET(dRange,AGGREGATE(15,7, ROW(dRange)/(--(dRange=$L1)), COLUMN()-COLUMN($L$1))-1,-1),"")
This assumes the Unique list column is in L1 and this is in column M
Oct 16 2020 02:15 PM
The difference is in using OFFSET() instead of INDEX(). These two functions often compete, but I prefer the latest.
Oct 16 2020 02:50 PM
@Sergei Baklan ok so you made me look. lol.
actually there are a couple differences in how we did it.
as you noted the difference between INDEX vs OFFSET. I suppose index is more 'appropriate' here since it picks a single reference instead of shifting the range of references. Maybe it is slightly more efficient? But they are both volatile right?
also your equation looks at the list it has created to find next unique value while mine ranks all the unique values and uses the row to pick the next unique value in that sequence. Again yours might be a little better since you always select the 1st in the resulting list and hence probably more efficient.
This is why I love this forum, it helps me see other and often better ways of doing things in Excel
Oct 16 2020 03:05 PM
@mtarler , I know both approaches just INDEX() is more comfortable for me and that's the only reason I prefer to work with it. On really big workbooks performance is the core, with the rest as with vodka - we know is it good or bad only next morning - we know good or bad solution mainly on maintenance phase. We may expect and give our estimation, but here some depends on personalities. What is good for one person could be not very suitable for another one.
What is really good in Excel is that everything could be done by several ways.
Oct 16 2020 03:27 PM
Oct 17 2020 02:57 AM
Details are here Excel performance: Improving calculation performance