Questions

I want to ask about a formula. Is any way to do?

+
2 Votes

I want to ask about a formula. Is any way to do?

waqas79
I have first column in excel for specific entry like, "c", "p", etc, I want a formula which can only handles the cells of that row where "c" exist in first column. Actually i want to copy the other cells information of that specific row where "c" occur.

Member Answers

    • +
      1 Votes
      Scalloway1

      If "c" is always the first character in the cell you could use this. It assumes that your first item of data is in A1

      =IF(LEFT(+A1,1)="c",

      Then type in what you want to do after the ","

      +
      0 Votes
      waqas79

      Thanks for the reply, but sorry perhaps i could not explain exactly what i want. I try again. I have a large data about a the sale detail of an item. Every customer data collect in separate row. I want to collect information of specific customer for which i have made 1st column in which i put any unique letter to call that row. I don't know the way how to call other cells of that row with this specific letter or this is right way to getting information. e.g., i want to copy or sum or any other formula to get C5, D5 or E5 data for which i put "p" in A5. Perhaps you will understand, now tell me if i am trying in right way or not, and guide me what will be possible solution. Regards.

      +
      1 Votes
      Scalloway1

      If you want to put the formula down every row but only show those with a specific letter then use this for text

      =IF(+A5="p",C5&" "&D5&" "&E5," ")

      If you want to add numbers together to get a total

      =IF(+A5="p",+C5+D5+E5,0)

      +
      1 Votes
      jojo2000828

      I suggest to use SUMIF instead. Give me a sign if you need more help.
      Sam

      +
      1 Votes
      gechurch

      There are a couple of ways of handling this. One would be to use filtering (highlight your heading row, click the Data tab in the ribbon and click the Filter icon). You can then select the dropdown box on the heading of the column containing your c's and p's and untick the p's. Excel will now only display rows where the first column is a 'c'. You would typically use this in combination with the SUBTOTAL formula for your other columns. For example, if column B contained the total sale amount, using SUBTOTAL at the end of that column will show you the total for only the rows that are displayed (that is, only the rows that start with a 'c').

      If you just want to sum a column or two and you do not want to hide rows as you would if you used a filter, then Jojo's suggestion of using SUMIF (or similar formula's like AVERAGEIF) is spot on. Lets say you have two columns; column A contains either 'c' or 'p', and column B contains the sale total. And you have rows 2 through 10 with values. You'd use SUMIF by entering the following formula in cell B11:
      =SUMIF(A2:A10,"=c",B2:B10)

      That formula says "look at column A for each row 2 through 10. If the value of column A equals c then include the value of column B in the SUM".

      +
      0 Votes
      waqas79

      Thanks again for reply, sorry for late reply from my side as i was busy. This help me a bit, Now i tell you the scenario of my data, I have a data consisting of several rows and ten (10) columns. I have made a performa on next sheet to get information of a single separate row. I have made 10 blocks in it as my row has 10 columns. My three columns have alphabets and 07 columns have digits. I only want to copy of that 10 columns data of a specific row in my performa blocks, but problems is that user will enter the data and he can't change the row number in formula, for this i have add a column to start and tell user to enter letter "p" in that row for which you want to print that performa. So help me that which kind of formula i can use which will automatic collect that row's information where user put "p". Or there is any other way to get information as i am trying a difficulty way or wrong way.
      Regards

      +
      0 Votes
      gechurch

      I'm still not clear on exactly what you are trying to do. Are you simply trying to pull out the values in those ten columns for a given row, and display them on a separate sheet? Or are you using a separate sheet to gather the input from the user, and are trying to save that to a new row on the first sheet? Or something different altogether?

      +
      0 Votes
      waqas79

      Yes, i simply trying to pull out the values in those ten columns for a given row which is the information of a particular customer and then print this information, user will input the data and on demand will print this, but problem is only this that there is no specific row number which i can put in formula, row number can be at random. For this purpose i am trying to do (may be wrong attempt) by creating a column at start and put a letter "p" for to the row to print and use the "p" in formula in some way.

      +
      0 Votes
      gechurch

      I'm still not clear. You say you want to pull all the data from an existing row, but now you're saying they are entering the data? It sounds like you want the user to see all the rows and columns. They would then go through them and add a 'p' to any rows they want printed, then they press a button and all the data for any row with a 'p' will be printed. Is that correct? If so the first response I gave of using data filtering is the way to go (ie. filter where column contains 'p', then hit the Print button). If you are trying to provide a different sheet with a nicer looking interface that the user sees instead of seeing the table with the data then you're going to need to do some VBA coding.

      +
      0 Votes
      waqas79

      I have a data consisting several customers’ detail, each row has different customer detail. My owner says that I want information of customer’s with the columns tags. For this purpose on separate sheet, I have made a Receipt Format with each column tag and left blank box ahead for its information to fill. I have no problem to fill that blank boxes with a formula by putting that specific row number or just copy paste the information. But in my absence the next person who will fill it have no idea to use formula or copy past. This is the complete scenario. Now tell me the way that is there any formula or way to auto fill these blanks boxes as user enter the data in the row or just want to print an existing data. I could not find the way that how to call this randomly selected row in the formula. For this I have created a column and thought that use a letter “p” to call the desire row in formula if possible and put that formula in those blanks boxes of Receipt Format. Now as anyone put “p” in any single row (I don’t want many at time), then blanks boxes of Receipt Format will be automatically filled, and user will just print it. It was my thought without knowing that it is possible or not as I have used this kind of alphabets in ‘IF’ formula, but that was some different scenario. But I simply know that “IF’’ formula can handle alphabets. Extremely sorry as I am not enough familiar with this that’s way facing difficulty to explain my problem exactly.

      +
      0 Votes
      gechurch

      Ok, thanks for the clarification. As I understand it you have just one row of data that you ever want to display/print at any time (and you are just putting it on a second sheet so it can be formatted for printing).

      That's fine, and there are a few ways to handle it. For all examples lets assume Sheet1 is where your rows of data are stored, and Sheet2 is where you want to display the particular data. On Sheet1 lets say column B stores first name, column C stores surname, column D stores address and column E stores city.

      Method 1) Have the user manually enter the row number to pull the data from on your sheet to be printed. ie. have a label in Sheet2!A1 that says "Row # to print" and have the user enter the row number into cell Sheet2!B1.

      You would then use the INDEX formula. This formula takes in an array of data (in our example, this will be Sheet1!A1:E10), a row number (this will be that value typed in to Sheet2!A2) and a column #. On Sheet2 click on the cell where you want the first name to be displayed and enter the formula "=INDEX(Sheet1!$A$1:$E$10,$B$1,1)". That formula says "here's my array, now return me the value of column #1 in the row I specified in cell B1 on this sheet". Now click wherever you want the surname to be displayed and enter the formula "=INDEX(Sheet1!$A$1:$E$10,$B$1,2)". You'll notice that's the exact same formula, but the last value is a 2 now, meaning "grab the value of column #2 in the row I specified". Or in other words "return the value in the surname column". Continue on like this until you are pulling in every column of data.

      Method 2) is a similar approach to above. This time though we are going to have the user type a 'p' in column #1 on Sheet1 for the row they want to print. We will then write a formula that looks for the row with a 'p' in column #1 and will read the values for each column in this row.

      To do so, enter the following formula on Sheet2 in the cell where you want the first name to appear "=VLOOKUP("p",Sheet1!A$1:$E$10,2,FALSE)". That formula says to Excel "Look for the value 'p' in the first column of the table Sheet1!A1:E10. Once you've found the row with the 'p', return the value in column #2 of that row". Obviously to return the surname you'll change the formula to "=VLOOKUP("p",Sheet1!A$1:$E$10,3,FALSE)" (to get the value of column #3) and so on.


      There are a few other strategies, but the ones I can think of are basically just variations of the above.

      +
      0 Votes
      waqas79

      Thank You very much! its help alot. Both methods are good. But i will use 2nd method as it fulfill my requirement 100%.
      In the same scenario please tell me if it possible I can also be able to copy the heading of any column of that specific in which the data entered by user.

      +
      0 Votes
      gechurch

      Grabbing the column headings is simple since you know exactly which cell they are in. Your headings will all be in the first row of Sheet1, so all you need to do is hit "=" then enter the cell containing the heading.

      For example (assuming the same layout of columns as I posted earlier) to grab the heading "First Name" you'd use the formula "=Sheet1!B1". To grab the surname heading you'd use "=Sheet1!C1" etc.

    • +
      1 Votes
      Scalloway1

      If "c" is always the first character in the cell you could use this. It assumes that your first item of data is in A1

      =IF(LEFT(+A1,1)="c",

      Then type in what you want to do after the ","

      +
      0 Votes
      waqas79

      Thanks for the reply, but sorry perhaps i could not explain exactly what i want. I try again. I have a large data about a the sale detail of an item. Every customer data collect in separate row. I want to collect information of specific customer for which i have made 1st column in which i put any unique letter to call that row. I don't know the way how to call other cells of that row with this specific letter or this is right way to getting information. e.g., i want to copy or sum or any other formula to get C5, D5 or E5 data for which i put "p" in A5. Perhaps you will understand, now tell me if i am trying in right way or not, and guide me what will be possible solution. Regards.

      +
      1 Votes
      Scalloway1

      If you want to put the formula down every row but only show those with a specific letter then use this for text

      =IF(+A5="p",C5&" "&D5&" "&E5," ")

      If you want to add numbers together to get a total

      =IF(+A5="p",+C5+D5+E5,0)

      +
      1 Votes
      jojo2000828

      I suggest to use SUMIF instead. Give me a sign if you need more help.
      Sam

      +
      1 Votes
      gechurch

      There are a couple of ways of handling this. One would be to use filtering (highlight your heading row, click the Data tab in the ribbon and click the Filter icon). You can then select the dropdown box on the heading of the column containing your c's and p's and untick the p's. Excel will now only display rows where the first column is a 'c'. You would typically use this in combination with the SUBTOTAL formula for your other columns. For example, if column B contained the total sale amount, using SUBTOTAL at the end of that column will show you the total for only the rows that are displayed (that is, only the rows that start with a 'c').

      If you just want to sum a column or two and you do not want to hide rows as you would if you used a filter, then Jojo's suggestion of using SUMIF (or similar formula's like AVERAGEIF) is spot on. Lets say you have two columns; column A contains either 'c' or 'p', and column B contains the sale total. And you have rows 2 through 10 with values. You'd use SUMIF by entering the following formula in cell B11:
      =SUMIF(A2:A10,"=c",B2:B10)

      That formula says "look at column A for each row 2 through 10. If the value of column A equals c then include the value of column B in the SUM".

      +
      0 Votes
      waqas79

      Thanks again for reply, sorry for late reply from my side as i was busy. This help me a bit, Now i tell you the scenario of my data, I have a data consisting of several rows and ten (10) columns. I have made a performa on next sheet to get information of a single separate row. I have made 10 blocks in it as my row has 10 columns. My three columns have alphabets and 07 columns have digits. I only want to copy of that 10 columns data of a specific row in my performa blocks, but problems is that user will enter the data and he can't change the row number in formula, for this i have add a column to start and tell user to enter letter "p" in that row for which you want to print that performa. So help me that which kind of formula i can use which will automatic collect that row's information where user put "p". Or there is any other way to get information as i am trying a difficulty way or wrong way.
      Regards

      +
      0 Votes
      gechurch

      I'm still not clear on exactly what you are trying to do. Are you simply trying to pull out the values in those ten columns for a given row, and display them on a separate sheet? Or are you using a separate sheet to gather the input from the user, and are trying to save that to a new row on the first sheet? Or something different altogether?

      +
      0 Votes
      waqas79

      Yes, i simply trying to pull out the values in those ten columns for a given row which is the information of a particular customer and then print this information, user will input the data and on demand will print this, but problem is only this that there is no specific row number which i can put in formula, row number can be at random. For this purpose i am trying to do (may be wrong attempt) by creating a column at start and put a letter "p" for to the row to print and use the "p" in formula in some way.

      +
      0 Votes
      gechurch

      I'm still not clear. You say you want to pull all the data from an existing row, but now you're saying they are entering the data? It sounds like you want the user to see all the rows and columns. They would then go through them and add a 'p' to any rows they want printed, then they press a button and all the data for any row with a 'p' will be printed. Is that correct? If so the first response I gave of using data filtering is the way to go (ie. filter where column contains 'p', then hit the Print button). If you are trying to provide a different sheet with a nicer looking interface that the user sees instead of seeing the table with the data then you're going to need to do some VBA coding.

      +
      0 Votes
      waqas79

      I have a data consisting several customers’ detail, each row has different customer detail. My owner says that I want information of customer’s with the columns tags. For this purpose on separate sheet, I have made a Receipt Format with each column tag and left blank box ahead for its information to fill. I have no problem to fill that blank boxes with a formula by putting that specific row number or just copy paste the information. But in my absence the next person who will fill it have no idea to use formula or copy past. This is the complete scenario. Now tell me the way that is there any formula or way to auto fill these blanks boxes as user enter the data in the row or just want to print an existing data. I could not find the way that how to call this randomly selected row in the formula. For this I have created a column and thought that use a letter “p” to call the desire row in formula if possible and put that formula in those blanks boxes of Receipt Format. Now as anyone put “p” in any single row (I don’t want many at time), then blanks boxes of Receipt Format will be automatically filled, and user will just print it. It was my thought without knowing that it is possible or not as I have used this kind of alphabets in ‘IF’ formula, but that was some different scenario. But I simply know that “IF’’ formula can handle alphabets. Extremely sorry as I am not enough familiar with this that’s way facing difficulty to explain my problem exactly.

      +
      0 Votes
      gechurch

      Ok, thanks for the clarification. As I understand it you have just one row of data that you ever want to display/print at any time (and you are just putting it on a second sheet so it can be formatted for printing).

      That's fine, and there are a few ways to handle it. For all examples lets assume Sheet1 is where your rows of data are stored, and Sheet2 is where you want to display the particular data. On Sheet1 lets say column B stores first name, column C stores surname, column D stores address and column E stores city.

      Method 1) Have the user manually enter the row number to pull the data from on your sheet to be printed. ie. have a label in Sheet2!A1 that says "Row # to print" and have the user enter the row number into cell Sheet2!B1.

      You would then use the INDEX formula. This formula takes in an array of data (in our example, this will be Sheet1!A1:E10), a row number (this will be that value typed in to Sheet2!A2) and a column #. On Sheet2 click on the cell where you want the first name to be displayed and enter the formula "=INDEX(Sheet1!$A$1:$E$10,$B$1,1)". That formula says "here's my array, now return me the value of column #1 in the row I specified in cell B1 on this sheet". Now click wherever you want the surname to be displayed and enter the formula "=INDEX(Sheet1!$A$1:$E$10,$B$1,2)". You'll notice that's the exact same formula, but the last value is a 2 now, meaning "grab the value of column #2 in the row I specified". Or in other words "return the value in the surname column". Continue on like this until you are pulling in every column of data.

      Method 2) is a similar approach to above. This time though we are going to have the user type a 'p' in column #1 on Sheet1 for the row they want to print. We will then write a formula that looks for the row with a 'p' in column #1 and will read the values for each column in this row.

      To do so, enter the following formula on Sheet2 in the cell where you want the first name to appear "=VLOOKUP("p",Sheet1!A$1:$E$10,2,FALSE)". That formula says to Excel "Look for the value 'p' in the first column of the table Sheet1!A1:E10. Once you've found the row with the 'p', return the value in column #2 of that row". Obviously to return the surname you'll change the formula to "=VLOOKUP("p",Sheet1!A$1:$E$10,3,FALSE)" (to get the value of column #3) and so on.


      There are a few other strategies, but the ones I can think of are basically just variations of the above.

      +
      0 Votes
      waqas79

      Thank You very much! its help alot. Both methods are good. But i will use 2nd method as it fulfill my requirement 100%.
      In the same scenario please tell me if it possible I can also be able to copy the heading of any column of that specific in which the data entered by user.

      +
      0 Votes
      gechurch

      Grabbing the column headings is simple since you know exactly which cell they are in. Your headings will all be in the first row of Sheet1, so all you need to do is hit "=" then enter the cell containing the heading.

      For example (assuming the same layout of columns as I posted earlier) to grab the heading "First Name" you'd use the formula "=Sheet1!B1". To grab the surname heading you'd use "=Sheet1!C1" etc.