Need help with filtering and using that data to search

[gueva]

I'm quite newish to excel but here goes. Below is an example of the data I'm working with:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]ID[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]A1-01-01[/TD]
[TD]2356487[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A1-01-02[/TD]
[TD]2356487[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A1-01-03[/TD]
[TD]7656732[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B1-01-01[/TD]
[TD]7656732[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B1-01-02[/TD]
[TD]2356487[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


The B1 locations hold IDs which already exist in A1 locations. They are replenishment for when A1 stock gets low.
I'm working with thousands of locations which are updated frequently so it's difficult to keep up with the data manually.

Ideally, I would like an end result that looks something like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Locations[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]2356487[/TD]
[TD]A1-01-01[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A1-01-02[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B1-01-02[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]7656732[/TD]
[TD]A1-01-03[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B1-01-01[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

I only want the table to contain IDs that exist in both A1 and B1 locations. I'm guessing I'd have to use a pivot table in some way. But I don't how to only grab the ID's that exist in B1 and then use that output to also show which A1 locations they exist in.

Could anyone point me in the right direction?

Thanks a bunch!

 

[jarjarbingie]

I'm quite newish to excel but here goes. Below is an example of the data I'm working with:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]ID[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]A1-01-01[/TD]
[TD]2356487[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A1-01-02[/TD]
[TD]2356487[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A1-01-03[/TD]
[TD]7656732[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B1-01-01[/TD]
[TD]7656732[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B1-01-02[/TD]
[TD]2356487[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


The B1 locations hold IDs which already exist in A1 locations. They are replenishment for when A1 stock gets low.
I'm working with thousands of locations which are updated frequently so it's difficult to keep up with the data manually.

Ideally, I would like an end result that looks something like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Locations[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]2356487[/TD]
[TD]A1-01-01[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A1-01-02[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B1-01-02[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]7656732[/TD]
[TD]A1-01-03[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B1-01-01[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

I only want the table to contain IDs that exist in both A1 and B1 locations. I'm guessing I'd have to use a pivot table in some way. But I don't how to only grab the ID's that exist in B1 and then use that output to also show which A1 locations they exist in.

Could anyone point me in the right direction?

Thanks a bunch!

say your raw data is in Sheet1 columns A:C,

D2 = LEFT($A2,2)
E2 = AND(COUNTIFS($B$2:$B$6,$B2,$D$2:$D$6,"A1")>0,COUNTIFS($B$2:$B$6,$B2,$D$2:$D$6,"B1")>0)

create a pivot table and drag column into the filter section and select TRUE