Extract values from a column

Hi everyone,

I have a column that consists of the following Measuring Instrumental (M.I) information under column A:

PG
PG
TPG
CMM
CMM
CMM
SRT
CMM
TPG
PG
TM
TM
CMM
SRC
SRT
TPGSC
PGSC
CMM
CMM
CMM
VI

I have 3 objectives:
i) Extract unique value for each M.I type to column B
- I have already achieved it by using the below formula:
=LOOKUP(2,1/((COUNTIF($B$1:B1,$A$2:$A$34)=0)*(COUNTIF($A$2:$A$34,$A$2:$A$34)>1)),$A$2:$A$34)

ii) For PG and TPG, I need to extract the same amount of times they appeared under column A to B, instead of just showing them once.

iii) For VI, I am not intending to extract it.

How do I modify my formula so it achieves all 3 objectives?

Much appreciated.

Please attach a workbook to make it easier to see,

Hi thank you very much for your reply.

I have attached a sample workbook under manage attachment.

Let me know again if you didn see it.

Jude

Welcome to the Excel Forum.

Try this array formula. Copy and paste in B2, confirm with Ctrl+Shift+Enter instead of just Enter then copy down:

Formula:

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$34,MATCH(1,(COUNTIF(B$1:B1,$A$2:$A$34)=0)*($A$2:$A$34<>"")*($A$2:$A$34<>"VI"),0)),INDEX($A$2:$A$34,AGGREGATE(15,6,(ROW($A$2:$A$34)-1)/($A$2:$A$34="PG"),1+COUNTIF(B$1:B1,"PG")))),INDEX($A$2:$A$34,AGGREGATE(15,6,(ROW($A$2:$A$34)-1)/($A$2:$A$34="TPG"),1+COUNTIF(B$1:B1,"TPG")))),"")

Good luck!

Hi Estevaoba!

Thank you very much for your help!

I tried and it works. However, when I applied the formula in another workbook under Column F, both PG and TPG values do not duplicate more than once, even though there are multiple similar inputs under column F.

In addition, 0 appears. When I tried this formula in another workbook, 0 does not appear in cells with no values.

Could you assist with this? I have attached the aforementioned workbook for your reference.

Jude

Hi Quang,

Thanks for responding and providing a solution to my query. I tried and it works too!

The only issue I had was that 0 will appear right after both PGs and TPGs before other Measuring Instrument types (CMM, SRC, etc) were extracted.

Can I trouble you to help me rectify this? I have attached the example workbook for your reference.


Warm Regards,
Jude

THANK YOU SO MUCH QUANG! IT WORKS PERFECTLY 100%!

Last query. How do I extract the information under Nominal, Upper, and Lower columns, which belong to M.I TPG and PG types, to rows 111, 112, and 113 respectively, under the section Measuring Instrument Used?

Subsequently, whenever there are new entries with either PG or TPG type, the Nominal, Upper, and Lower info, which belong to the new entries, will be extracted and pasted beside its respectively M.I type.

I have attached my workbook and filled up the cells in yellow for your ease of convenience.

Jude

Sure no problem Mr. Quang.

Jude

You're welcome. Glad to hear that bebo02199 has helped you with those more complex scenarios.
Thank you for the feedback and for the reputation added.
Have a blessed day!

Once again, thanks Quong! It works!

Just that I was hoping to only extract the Nominal and tolerance information for PG and TPG M.I type items. As for the rest of the M.I type items (e.g CMM, SRT), I am hoping to extract their respective full name, which I had indicated in Column Q to X. (E.g for M.I type CMM under Column F114, I am hoping to extract "CMM" from Column R15, and for M.I type SRT under Column F115, I am hoping to extract "Surface Roughness Test" from Column X11 etc)000

I have attached my workbook for your reference. I have also highlighted those I need to extract in yellow.

Let me know if you have any doubts or need further clarification, and I would be most glad to assist.

For M.I type CMM under Column F, it will be extracting the value indicated in cell R15 to cell B114. Currently, I just put "CMM" in cell R15 for now but the value may be subject to changes in the future.

To summarize, apart of PG and TPG, which will extract the nominal, upper and lower tolerance value from the above rows, other M.I types which you see from row Q to row X, will be extracted to column B111 and below, should the respective column F indicate its M.I type. (E.g If Column F120 indicates SRC, B120 will extract cell X10, which contains "Surface Roughness Comparator". If Column F121 indicates BG, B120 will extract cell R12, which contains "Bore Gauge".

Thank you very much Mr. Quang.

Everything works fine and well. All queries I had raised have been resolved

But I encountered a problem when I tried to copy the M.I stated in the attached workbook titled "B" into your latest workbook. I got an #NUM! error.

However, when I try to key in the M.I input manually or using pasting M.I value from another attached workbook titled "A", the formulas you provided work fine.

For your convenience, I have highlighted the cell in blue which I have pasted from "A", whereas cell in orange was pasted from "B".

Let me know if there is any confusion on my side and I would be glad to assist.

Jude

Hi Mr. Quang,

Just to inform you I have already rectified this issue which I raised on the 30th of June.

Once again, thank you very very much for your guidance throughout these weeks.

Jude

Hi Mr. Quang,

Very sorry to bother you again.

A few months back you gave me bunch of solutions to tackle my issue and i am indeed very grateful.

Just to recap, if you refer to my excel file, there were a number of different types of (MI) Measuring Instrument types (TPG, PG, CMM SRT etc)

Previously, I wanted to achieve items with PG,TPG,TRG,RG or GB MI type will repeat themselves under the lower section: MEASURING INSTRUMENT USED at cell B110

In addition, items with other types of MI will only repeat once e.g CMM, SRT, TM etc

With your help previously, my current excel formula has achieved the following:
1) Items will either TPG, PG, TRG, RG, GB will appear depending on the number of times it appears in the upper section
2) Items with other types of MI will only repeat once
3) Items with VI and NOTE type of MI will not appear at all


However, I realized I needed to modify my existing formula by ensuring the following:
a) item with "ID" indicated under the L/N will not appear, regardless of MI type. This condition is similar to No. 3 (Please refer to the cell in yellow in the attached workbook), although its MI type is PG. Based on the current formula, it will appear under section "MEASURING INSTRUMENT USED"

b) Similar items which appear more than once will only appear once, regardless of MI type. This condition is similar to No. 2 (Please refer to cell in blue in the attached workbook), although its MI type is TPG. Based on the current formula, it will appear 4 times under section "MEASURING INSTRUMENT USED", similarly to the number of times I have added in the upper section.

Much appreciated.

Jude

Originally Posted by JeteMc

This proposal employs two helper columns which may be hidden for aesthetic purposes.
1. Column Q is populated using: =IF(OR(A8="",ISNUMBER(SEARCH("id",A8))),"",IF(IFERROR(SUMPRODUCT(--(Q$7:Q7=INT(A8)))>0,0),"",INT(A8)))
2. Column R is populated using: =IF(Q8<>"",F8,"")
3. The references in the formula that populates F111 and down are changed from $F$8:$F$108 to $R$8:$R$108
Let us know if you have any questions.

Hi JeteMC,

Your solution works!

Just FYI I realized I also had to modify the formula which populates cells B111, D111, and E111, in order to ensure their information matches the M.I type after using your solution.

I shall proceed to close this thread.

Once again, thank you so much for dropping by to provide me with these solutions.

May God bless you.

Jude