Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Good day all, hope you can help!!
I need a formula that will take info from sheet A column b only if sheet A column A contains "EMC" somewhere in the text. I need it to paste into Sheet B coumn B exactly as shown in sheet A column b as well as index to the next line that contains this info. example: Sheet A A B EMC, Safe PREPARE Checklist ENVIR Send PCB for test ID, EMC, SAFE Testing files complete Sheet B: A B PREPARE Checklist Testing files complete I have tried using the following formula but I get empty lines "FALSE" between the actual info and it is not pulling the information exactly as it is on the other sheet. It is also not pulling all info from lines that contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does not pull that line item. =IF(Sheet!A6="EMC",Sheet!B6) Help, what am I doing wrong? I am a newbie and have spent several hours looking through all the formulas/discussions you have provided to other users but did not see one that would do what I am looking for on this sheet. Your help would be appreciated. Thanks, Trese |
#2
![]() |
|||
|
|||
![]()
One way which should do it ..
In SheetA ----- Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") ) Copy D1 down to say, D100, to cover the max expected data range in col A (can copy down ahead of expected data in col A) In SheetB ---- Put in B1: =IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL (SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0))) Copy B1 down to B100 (cover the same range size as in col D in SheetA) Col B will return the desired results from SheetA's col B, all neatly bunched at the top .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Trese" wrote in message ... Good day all, hope you can help!! I need a formula that will take info from sheet A column b only if sheet A column A contains "EMC" somewhere in the text. I need it to paste into Sheet B coumn B exactly as shown in sheet A column b as well as index to the next line that contains this info. example: Sheet A A B EMC, Safe PREPARE Checklist ENVIR Send PCB for test ID, EMC, SAFE Testing files complete Sheet B: A B PREPARE Checklist Testing files complete I have tried using the following formula but I get empty lines "FALSE" between the actual info and it is not pulling the information exactly as it is on the other sheet. It is also not pulling all info from lines that contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does not pull that line item. =IF(Sheet!A6="EMC",Sheet!B6) Help, what am I doing wrong? I am a newbie and have spent several hours looking through all the formulas/discussions you have provided to other users but did not see one that would do what I am looking for on this sheet. Your help would be appreciated. Thanks, Trese |
#3
![]() |
|||
|
|||
![]()
Max,
Thanks for the quick response. I loaded these formulas into a sample sheet but only had luck with the formula on sheet A. Sheet B formula did not pull in any data. When I enter the formula EXCEL changes it and asks that I update the file. Not sure why? I can send you the sample sheet for reference if you like. Any ideas Thanks, Trese "Max" wrote: One way which should do it .. In SheetA ----- Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") ) Copy D1 down to say, D100, to cover the max expected data range in col A (can copy down ahead of expected data in col A) In SheetB ---- Put in B1: =IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL (SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0))) Copy B1 down to B100 (cover the same range size as in col D in SheetA) Col B will return the desired results from SheetA's col B, all neatly bunched at the top .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Trese" wrote in message ... Good day all, hope you can help!! I need a formula that will take info from sheet A column b only if sheet A column A contains "EMC" somewhere in the text. I need it to paste into Sheet B coumn B exactly as shown in sheet A column b as well as index to the next line that contains this info. example: Sheet A A B EMC, Safe PREPARE Checklist ENVIR Send PCB for test ID, EMC, SAFE Testing files complete Sheet B: A B PREPARE Checklist Testing files complete I have tried using the following formula but I get empty lines "FALSE" between the actual info and it is not pulling the information exactly as it is on the other sheet. It is also not pulling all info from lines that contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does not pull that line item. =IF(Sheet!A6="EMC",Sheet!B6) Help, what am I doing wrong? I am a newbie and have spent several hours looking through all the formulas/discussions you have provided to other users but did not see one that would do what I am looking for on this sheet. Your help would be appreciated. Thanks, Trese |
#4
![]() |
|||
|
|||
![]()
... Sheet B formula did not pull in any data. ..
You probably were hit by the line break(s)/wrap(s) in the formula when you copied and pasted into SheetB. These break(s) need to be corrected manually after pasting. The entire formula has to be in one line. Try it again. And just in case needed, here's a link to a sample file with the implemented construct: http://savefile.com/files/6733373 Trese_wksht.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Trese" wrote in message ... Max, Thanks for the quick response. I loaded these formulas into a sample sheet but only had luck with the formula on sheet A. Sheet B formula did not pull in any data. When I enter the formula EXCEL changes it and asks that I update the file. Not sure why? I can send you the sample sheet for reference if you like. Any ideas Thanks, Trese "Max" wrote: One way which should do it .. In SheetA ----- Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") ) Copy D1 down to say, D100, to cover the max expected data range in col A (can copy down ahead of expected data in col A) In SheetB ---- Put in B1: =IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL (SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0))) Copy B1 down to B100 (cover the same range size as in col D in SheetA) Col B will return the desired results from SheetA's col B, all neatly bunched at the top .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Trese" wrote in message ... Good day all, hope you can help!! I need a formula that will take info from sheet A column b only if sheet A column A contains "EMC" somewhere in the text. I need it to paste into Sheet B coumn B exactly as shown in sheet A column b as well as index to the next line that contains this info. example: Sheet A A B EMC, Safe PREPARE Checklist ENVIR Send PCB for test ID, EMC, SAFE Testing files complete Sheet B: A B PREPARE Checklist Testing files complete I have tried using the following formula but I get empty lines "FALSE" between the actual info and it is not pulling the information exactly as it is on the other sheet. It is also not pulling all info from lines that contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does not pull that line item. =IF(Sheet!A6="EMC",Sheet!B6) Help, what am I doing wrong? I am a newbie and have spent several hours looking through all the formulas/discussions you have provided to other users but did not see one that would do what I am looking for on this sheet. Your help would be appreciated. Thanks, Trese |
#5
![]() |
|||
|
|||
![]()
In SheetA
----- Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") ) Just some clarifications: I presumed you wanted the search for "EMC" to be case sensitive. That's why FIND was used in the above formula. If however, you need it to be case-insensitive, just replace FIND with SEARCH in the formula. And instead of hardcoding the target phrase "EMC" into the formula, we could point the formula at an adjacent cell, say: E1, and enter the target phrase in E1. This "softcoding" is useful if we have to do the same thing over for other target phrases. Then just put instead in D1: =IF(A1="","",IF(ISNUMBER(FIND($E$1,A1)),ROW(),"")) and copy D1 down as before -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
![]() |
|||
|
|||
![]()
Max,
IT WORKED!!! I thought I corrected the line breaks but I guess I missed something the first time around. Anyway- thanks for your time and your help- I REALLY appreciate it. Trese "Max" wrote: In SheetA ----- Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") ) Just some clarifications: I presumed you wanted the search for "EMC" to be case sensitive. That's why FIND was used in the above formula. If however, you need it to be case-insensitive, just replace FIND with SEARCH in the formula. And instead of hardcoding the target phrase "EMC" into the formula, we could point the formula at an adjacent cell, say: E1, and enter the target phrase in E1. This "softcoding" is useful if we have to do the same thing over for other target phrases. Then just put instead in D1: =IF(A1="","",IF(ISNUMBER(FIND($E$1,A1)),ROW(),"")) and copy D1 down as before -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
![]() |
|||
|
|||
![]()
Glad to hear that, Trese !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Trese" wrote in message ... Max, IT WORKED!!! I thought I corrected the line breaks but I guess I missed something the first time around. Anyway- thanks for your time and your help- I REALLY appreciate it. Trese |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
I need to merge lots of info, w/o copy paste? | Excel Worksheet Functions | |||
copy formula referencing sheet name to another sheet | Excel Worksheet Functions | |||
How do I copy Page Setups from one Sheet to Another sheet? | Excel Worksheet Functions | |||
Copy 1 Sheet to Another | Excel Worksheet Functions | |||
Copy info from worksheet 1 to other sheets | Excel Worksheet Functions |