Extracting the correct information

[Noxqss38242]

I'm wanting an easier way to pull information I need for work, any solution to this would be awesome!

I extract information and paste it on "sheet 2" which contains hundreds of rows and about 15 columns. In column "a" is the list of truck numbers (sometimes and often repeated), in column "h" is another number that I want to extract to "sheet1" based off what I enter in cell "a1" on "sheet1". So "sheet2" will look like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD](Truck #)[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]Code[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD][/TD]
[TD]2467[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD][/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD][/TD]
[TD]2467[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD][/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD][/TD]
[TD]5717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD][/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And I want to enter on "sheet1" the truck number and have it pull the codes relating to that specific truck number as there will be hundreds of other trucks listed as well, it can be with the duplicate codes but I would prefer it to not duplicate codes already pulled. So it would look something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Truck #[/TD]
[TD][/TD]
[TD]Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19848[/TD]
[TD][/TD]
[TD]2467[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the truck number comes up with no match, then I would also like the cell to say, "no codes" or if easier, to just be blank and not an error.

Essentially I'm going to lock down every other cell so others can access this information and only the A1 cell would be available to them and codes (if any) will populate into those cells so the individual can see if a specific truck is having "codes"/issues.

Any solution to this? Thank you!

 

[Aladin Akyurek]

Let's name the range in Sheet2 which houses the codes as Code.
Let's also name the range in Sheet2 which houses the truck numbers as Truck.

Let A2 of Sheet1 house a truck number of interest like 19848.

In B2 of Sheet1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Truck=$A$2,Code),Code),1))

In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$2:C2)>$B$2,"",INDEX(Code,SMALL(IF(FREQUENCY(IF(ISNUMBER(Code),IF(Truck=$A$2,MATCH(Code,Code,0))),ROW(Code)-ROW(INDEX(Code,1,1))+1),ROW(Code)-ROW(INDEX(Code,1,1))+1),ROWS($C$2:C2))))

 

[Noxqss38242]

Awesome, it worked!!

 

[Peter_SSs]

With the same named ranges and the same formula in B2 of Sheet1, you could also try this standard-entry formula in C2, copied down.

=IF(ROWS(C$2:C2)>B$2,"",INDEX(Code,AGGREGATE(15,6,(ROW(Code)-ROW(INDEX(Code,1))+1)/((Truck=A$2)*(ISNA(MATCH(Code,C$1:C1,0)))),1)))

 

[Aladin Akyurek]

Awesome, it worked!!

Glad to help.

 

[Noxqss38242]

Okay, now I ran into another issue. I'm using the same cell (truck number) in A2 and I have a different sheet that trucks/codes are also stored. In cell D2 I put the first formula that is set for B2 but changed the name range so as to pull the information from "Vehicle" as opposed to "truck" and "Omnitracs" as opposed to "Code" but it comes up with "0".

=SUM(IF(FREQUENCY(IF(Vehicle=$A$2,Omnitracs),Omnitracs),1))

Likewise the next issue is also blank:
=IF(ROWS($E$2:E2)>$D$2,"",INDEX(Omnitracs,SMALL(IF(FREQUENCY(IF(ISNUMBER(Omnitracs),IF(Truck=$A$2,MATCH(Omnitracs,Omnitracs,0))),ROW(Omnitracs)-ROW(INDEX(Omnitracs,1,1))+1),ROW(Omnitracs)-ROW(INDEX(Omnitracs,1,1))+1),ROWS($D$2:D2))))

Also did the control+shift+enter

 

[Noxqss38242]

Okay, so the codes in this section contain letters then numbers. IE: SPN1787 and PRO2010 and PID190. When I took overwritten the cell with just numbers, the formula works. However, I need the whole code. Hmm....

Okay, D2 works only if numbers are listed like mentioned above but E2 will not pull the code still, possibly because the "range" contains letters still? Hmm...

 

[Aladin Akyurek]

Replace

=SUM(IF(FREQUENCY(IF(Vehicle=$A$2,Omnitracs),Omnitracs),1))

with:

=SUM(IF(FREQUENCY(IF(1-(Omnitracs=""),IF(Vehicle=$A$2,MATCH(Omnitracs,Omnitracks,0))),ROW(Omnitracs)-ROW(INDEX(Omnitracks,1,1))+1),1))

And

=IF(ROWS($E$2:E2)>$D$2,"",INDEX(Omnitracs,SMALL(IF(FREQUENCY(IF(ISNUMBER(Omnitracs),IF(Truck=$A$2,MATCH(Omnitracs,Omnitracs,0))),ROW(Omnitracs)-ROW(INDEX(Omnitracs,1,1))+1),ROW(Omnitracs)-ROW(INDEX(Omnitracs,1,1))+1),ROWS($D$2:D2))))

with:

=IF(ROWS($E$2:E2)>$D$2,"",INDEX(Omnitracs,SMALL(IF(FREQUENCY(IF(1-(Omnitracs=""),IF(Truck=$A$2,MATCH(Omnitracs,Omnitracs,0))),ROW(Omnitracs)-ROW(INDEX(Omnitracs,1,1))+1),ROW(Omnitracs)-ROW(INDEX(Omnitracs,1,1))+1),ROWS($D$2:D2))))

 

[Noxqss38242]

Okay, so I missed the "truck" to "vehicle". I corrected that...my mistake. I also had to separate letters from numbers in order for the formula to work. Is there a different formula that will pull the whole code including letters, ie: SPN102? Otherwise as I have it now, I separated the letters from numbers and ran the formula that way and it works perfectly.

Standby...I just saw your reply...working on your suggestion now

 

[Aladin Akyurek]

Okay, so I missed the "truck" to "vehicle". I corrected that...my mistake. I also had to separate letters from numbers in order for the formula to work. Is there a different formula that will pull the whole code including letters, ie: SPN102? Otherwise as I have it now, I separated the letters from numbers and ran the formula that way and it works perfectly.

Standby...I just saw your reply...working on your suggestion now

I made a typo though: Omnitracks >> Omnitracs