Reference Question

Copper Contributor


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.

16 Replies

@DonMirabella 

L1

=UNIQUE(Source!B1:B100)

 

M1

=TRANSPOSE(SORT(FILTER(Source!$A$1:$A$100,(Source!$B$1:$B$100=L1))))

 

@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

@mtarler We don't have Excel 365 so no Unique function available.

@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.

@DonMirabella 

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

image.png

@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

@DonMirabella 

One more variant

image.png

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

@DonMirabella 

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.

@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.

@DonMirabella 

I added measure in Power Pivot, preliminary assigned in it Text type to Number column

image.png

@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

@mtarler 

The difference is in using OFFSET() instead of INDEX(). These two functions often compete, but I prefer the latest.

@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 :)

@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.

From what I've read, index is not volatile (after Excel 97).