Forum Discussion
Solved
EXCEL Homework help
I have this homework for one of my classes and we have to use the VLOOKUP formula for these Here were the instructions given to us: Task 1: Complete lookup value from columns I:L of Transacti...
bbretton61 There is a rather simple solution that might be possible, using the TOROW and WRAPROWS functions, but it all depends on the consistency of your data...
1) Does every invoice row always have an accompanying payment row directly below it?
2) Is there ever more than one payment row per invoice?
3) Are there any blank rows that separate each invoice? If so, is the number of blank rows per invoice consistent for the entire data range?
Assuming the total number of rows per invoice is consistent throughout the entire spreadsheet, the following two examples would work.
Example 1: Data contains a consistent number of invoice and payment details
A B C D 1
INV001 11/01/2023 John Smith 2,500.00 2 Visa 2,500.00 3 4
INV002 11/02/2023 Jane Doe 1,500.00 5 Debit 1,500.00 6 In this example, since there are a consistent number of invoice details (4), as well as payment details (2), the following formula can be used to return all 6 items to one row for each invoice:
=WRAPROWS(TOROW(A1:D6, 1), 6)The TOROW function takes all of the data from the selected range and moves it to a single row. The optional [ignore] argument in this case is set to 1 to ignore blanks. The wrap_count argument of WRAPROWS is set to 6 to return a total of 6 items per row.
Example 2: Data contains a variable number of invoice or payment details
A B C D 1
INV001 11/01/2023 John Smith 2,500.00 2 Visa 1,250.00 MasterCard 1,250.00 3 4
INV002 11/02/2023 Jane Doe 1,500.00 5 Debit 1,500.00 6 In this example, since the total number of items varies for each invoice, the formula must be modified as follows:
=WRAPROWS(TOROW(A1:D6), 12)In order to return a consistent number of items per invoice, the optional [ignore] argument could not be used in this case. Instead, the wrap_count was determined by multiplying the number of columns in the data range by the number of rows per invoice, including blank rows (4 x 3 = 12). If there were no blank rows in the above example, the wrap_count would have been 8.
Note: any blank cells from the original data range will be filled with zeros in the resulting array.
Hello Hans,
Last time you helped I wanted to count the number of drives each team made per week throughout the 22-week season. That information came from the Driveworksheet, but it has data other than the number of drives. It also gives the results of those drives. Particularly, it tells you if the drive ended in a touchdown or a field goal.
What I want to do now is again count the number of drives and also count the number of touchdowns and field goals. It appeared to me that capturing that data on another worksheet would be best. That worksheet is named Drive Count.
I think that you can read the macro code and see how we get to the Drives. From there to get to the touchdowns and field goals you would offset 7 columns right. And you would be looking at the same row numbers that the drives were counted. For example: if the drives were in the range B12:B22 the touchdowns and field goals would be in I12:I22.
I hope that explains what I want to do. I'll try to clarify any questions you have.
https://imgur.com/1HKgCaS
I thought I would post this screenshot to show the data area in the above example.
I thought I would post this screenshot to show the data area in the above example.