Getting Data from big list with mulitple identical query to look for.

T

Turbo68

Board Regular
Joined
Jan 31, 2014
Messages
118
I am needing to see how I can get info from a long list of items that has can have multiple items in the query. Here is example of my data:

[TABLE="width: 817"]
<tbody>[TR]
[TD]store
[/TD]
[TD]reg
[/TD]
[TD]os
[/TD]
[TD]model
[/TD]
[TD]ram
[/TD]
[TD]Tech
[/TD]
[TD]Work Needed
[/TD]
[/TR]
[TR]
[TD]1005
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1005
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1005
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3494
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1005
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1005
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex XE2
[/TD]
[TD]3510
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Winston Williams
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1010
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Winston Williams
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1010
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 740 Enhanced
[/TD]
[TD]958
[/TD]
[TD]Winston Williams
[/TD]
[TD]UPGRADE OS
[/TD]
[/TR]
[TR]
[TD]1010
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Winston Williams
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Winston Williams
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1010
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex XE2
[/TD]
[TD]3510
[/TD]
[TD]Winston Williams
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1012
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1012
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3494
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1012
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1012
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Alex Mertz
[/TD]
[TD]CALL FOR REPLACEMENT
[/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex XE2
[/TD]
[TD]3510
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]7
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1108
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 740 Enhanced
[/TD]
[TD]3006
[/TD]
[TD]Rick Florke
[/TD]
[TD]CALL FOR REPLACEMENT
[/TD]
[/TR]
[TR]
[TD]1110
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 740 Enhanced
[/TD]
[TD]958
[/TD]
[TD]Rick Florke
[/TD]
[TD]UPGRADE OS
[/TD]
[/TR]
[TR]
[TD]1110
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1110
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1110
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex XE2
[/TD]
[TD]3510
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex XE2
[/TD]
[TD]3510
[/TD]
[TD]Rick Florke
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Rick Florke
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 740 Enhanced
[/TD]
[TD]3006
[/TD]
[TD]Rick Florke
[/TD]
[TD]CALL FOR REPLACEMENT
[/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3241
[/TD]
[TD]Alex Mertz
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]6
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]CALL FOR REPLACEMENT
[/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]3496
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]4
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 3010
[/TD]
[TD]1944
[/TD]
[TD]Alex Mertz
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]6
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1181
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 380
[/TD]
[TD]3291
[/TD]
[TD]Alex Mertz
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1183
[/TD]
[TD]1
[/TD]
[TD]Microsoft Windows 7 Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Alex Mertz
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1183
[/TD]
[TD]2
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1183
[/TD]
[TD]3
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1183
[/TD]
[TD]5
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Alex Mertz
[/TD]
[TD]RE-IMAGE MACHINE
[/TD]
[/TR]
[TR]
[TD]1183
[/TD]
[TD]254
[/TD]
[TD]Microsoft Windows XP Professional
[/TD]
[TD]OptiPlex 390
[/TD]
[TD]3240
[/TD]
[TD]Alex Mertz
[/TD]
[TD]CALL FOR REPLACEMENT
[/TD]
[/TR]
</tbody>[/TABLE]


I would like to have another tabbed page to show one stores data like the following when I type in a specific store number like i have here in RED. Then it will show all items from that store. If could be 3 or 4 lines up to 10 or 12 lines of info.

[TABLE="width: 636"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 6"]1170
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]REG[/TD]
[TD]OS[/TD]
[TD]Machine[/TD]
[TD]RAM[/TD]
[TD]FT[/TD]
[TD]WORK NEEDED[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Microsoft Windows XP Professional[/TD]
[TD]OptiPlex 3010[/TD]
[TD="align: right"]3496[/TD]
[TD]Alex Mertz[/TD]
[TD]RE-IMAGE MACHINE[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Microsoft Windows XP Professional[/TD]
[TD]OptiPlex 3010[/TD]
[TD="align: right"]3496[/TD]
[TD]Alex Mertz[/TD]
[TD]RE-IMAGE MACHINE[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Microsoft Windows 7 Professional [/TD]
[TD]OptiPlex 390[/TD]
[TD="align: right"]3241[/TD]
[TD]Alex Mertz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Microsoft Windows XP Professional[/TD]
[TD]OptiPlex 3010[/TD]
[TD="align: right"]3496[/TD]
[TD]Alex Mertz[/TD]
[TD]RE-IMAGE MACHINE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Microsoft Windows XP Professional[/TD]
[TD]OptiPlex 3010[/TD]
[TD="align: right"]3496[/TD]
[TD]Alex Mertz[/TD]
[TD]RE-IMAGE MACHINE[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Microsoft Windows XP Professional[/TD]
[TD]OptiPlex 3010[/TD]
[TD="align: right"]3496[/TD]
[TD]Alex Mertz[/TD]
[TD]RE-IMAGE MACHINE[/TD]
[/TR]
[TR]
[TD="align: right"]254[/TD]
[TD]Microsoft Windows XP Professional[/TD]
[TD]OptiPlex 3010[/TD]
[TD="align: right"]3496[/TD]
[TD]Alex Mertz[/TD]
[TD]CALL FOR REPLACEMENT[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated.

Mike
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
You can use an array formula but it depends on the size of your lookup table:


Book1
ABCDEFG
1storeregosmodelramTechWork Needed
210051Microsoft Windows XP ProfessionalOptiPlex 3903240Rick FlorkeRE-IMAGE MACHINE
310052Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
410053Microsoft Windows XP ProfessionalOptiPlex 30103494Rick FlorkeRE-IMAGE MACHINE
510054Microsoft Windows XP ProfessionalOptiPlex 3903240Rick FlorkeRE-IMAGE MACHINE
61005254Microsoft Windows 7 ProfessionalOptiPlex XE23510Rick Florke
710101Microsoft Windows XP ProfessionalOptiPlex 3903240Winston WilliamsRE-IMAGE MACHINE
810102Microsoft Windows XP ProfessionalOptiPlex 3803291Winston WilliamsRE-IMAGE MACHINE
910103Microsoft Windows XP ProfessionalOptiPlex 740 Enhanced958Winston WilliamsUPGRADE OS
1010104Microsoft Windows 7 ProfessionalOptiPlex 3803291Winston Williams
1110105Microsoft Windows XP ProfessionalOptiPlex 3803291Winston WilliamsRE-IMAGE MACHINE
121010254Microsoft Windows 7 ProfessionalOptiPlex XE23510Winston Williams
1310121Microsoft Windows XP ProfessionalOptiPlex 3803291Alex MertzRE-IMAGE MACHINE
1410122Microsoft Windows XP ProfessionalOptiPlex 30103494Alex MertzRE-IMAGE MACHINE
1510123Microsoft Windows XP ProfessionalOptiPlex 3803291Alex MertzRE-IMAGE MACHINE
161012254Microsoft Windows XP ProfessionalOptiPlex 3803291Alex MertzCALL FOR REPLACEMENT
1711081Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
1811082Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
1911083Microsoft Windows 7 ProfessionalOptiPlex 30103496Rick Florke
2011084Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
2111085Microsoft Windows 7 ProfessionalOptiPlex XE23510Rick Florke
2211087Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
231108254Microsoft Windows XP ProfessionalOptiPlex 740 Enhanced3006Rick FlorkeCALL FOR REPLACEMENT
2411101Microsoft Windows 7 ProfessionalOptiPlex 3803291Rick Florke
2511102Microsoft Windows XP ProfessionalOptiPlex 740 Enhanced958Rick FlorkeUPGRADE OS
2611103Microsoft Windows 7 ProfessionalOptiPlex 30103496Rick Florke
2711104Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
2811105Microsoft Windows XP ProfessionalOptiPlex 30103496Rick FlorkeRE-IMAGE MACHINE
291110254Microsoft Windows 7 ProfessionalOptiPlex XE23510Rick Florke
3011111Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
3111112Microsoft Windows XP ProfessionalOptiPlex 3803291Rick FlorkeRE-IMAGE MACHINE
3211113Microsoft Windows XP ProfessionalOptiPlex 3903240Rick FlorkeRE-IMAGE MACHINE
3311114Microsoft Windows 7 ProfessionalOptiPlex XE23510Rick Florke
3411115Microsoft Windows XP ProfessionalOptiPlex 30103496Rick FlorkeRE-IMAGE MACHINE
351111254Microsoft Windows XP ProfessionalOptiPlex 740 Enhanced3006Rick FlorkeCALL FOR REPLACEMENT
3611701Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
3711702Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
3811703Microsoft Windows 7 ProfessionalOptiPlex 3903241Alex Mertz
3911704Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
4011705Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
4111706Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
421170254Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzCALL FOR REPLACEMENT
4311811Microsoft Windows XP ProfessionalOptiPlex 3903240Alex MertzRE-IMAGE MACHINE
4411812Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
4511813Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
4611814Microsoft Windows XP ProfessionalOptiPlex 3803291Alex MertzRE-IMAGE MACHINE
4711815Microsoft Windows 7 ProfessionalOptiPlex 30101944Alex Mertz
4811816Microsoft Windows XP ProfessionalOptiPlex 3803291Alex MertzRE-IMAGE MACHINE
491181254Microsoft Windows 7 ProfessionalOptiPlex 3803291Alex Mertz
5011831Microsoft Windows 7 ProfessionalOptiPlex 3903240Alex Mertz
5111832Microsoft Windows XP ProfessionalOptiPlex 3903240Alex MertzRE-IMAGE MACHINE
5211833Microsoft Windows XP ProfessionalOptiPlex 3903240Alex MertzRE-IMAGE MACHINE
5311835Microsoft Windows XP ProfessionalOptiPlex 3903240Alex MertzRE-IMAGE MACHINE
541183254Microsoft Windows XP ProfessionalOptiPlex 3903240Alex MertzCALL FOR REPLACEMENT
Sheet1



Book1
ABCDEF
11170
2REGOSMachineRAMFTWORK NEEDED
31Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
42Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
53Microsoft Windows 7 ProfessionalOptiPlex 3903241Alex Mertz
64Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
75Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
86Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzRE-IMAGE MACHINE
9254Microsoft Windows XP ProfessionalOptiPlex 30103496Alex MertzCALL FOR REPLACEMENT
Sheet2
Cell Formulas
RangeFormula
A3{=IFERROR(INDEX(Sheet1!B$1:B$54,SMALL(IF(Sheet1!$A$1:$A$54=$A$1,ROW(Sheet1!$A$1:$A$54)),ROWS($A$3:$A3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


Copy formula in A3 across and down as far as necessary. You'll also need to change the $54 to be the last row of your data.

WBD
 
Upvote 0
I also needed to change the number format on Sheet2 column F to:

Code: 
0;-0;;@

Otherwise it will display zero instead of an empty cell.

WBD
 
Upvote 0
Alternatively, this works

assuming "store" is in Sheet1!A1
and your output is in Sheet2 with 1170 in Sheet2!A1

in Sheet2!A2
=IFERROR(INDEX(Sheet1!$A$2:$G$54,SMALL(IF((Sheet1!$A$2:$A$54=$A$1),ROW($A$2:$A$54)),ROW(A1))-(ROW(A$2)-1),COLUMN()+1),"")
Array formula, use Ctrl-Shift-Enter
copy across to column F
copy the row down for as many rows of output as you are expecting.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top