Appenda data from one sheet to another

[MASOODAHMAD]

Hi Everyone. Hope you're all well.

I'm looking for something. Can you please suggest some trick, formula or a macro to achieve this:

Source:
1. I have data (Job Details) in 'OperatorDetails' sheet, where Operators fill in the details on daily basis. For example:

[TABLE="width: 437"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:4522; width:106pt" span="2" width="106"> <col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 81"]Job Number[/TD]
[TD="class: xl64, width: 63"]Brand[/TD]
[TD="class: xl64, width: 106"]Status[/TD]
[TD="class: xl64, width: 106"]PrinterName[/TD]
[TD="class: xl64, width: 81"]Date Uploaded[/TD]
[/TR]
[TR]
[TD="class: xl65"]02786a_VEN[/TD]
[TD="class: xl65"]Thermal[/TD]
[TD="class: xl65"]Files Transferred[/TD]
[TD="class: xl65"]Printer01[/TD]
[TD="class: xl66"]4/30/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02787a_VEN[/TD]
[TD="class: xl65"]EQP[/TD]
[TD="class: xl65"]Rejected[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]02788a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]02789a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Files Transferred[/TD]
[TD="class: xl65"]Printer02[/TD]
[TD="class: xl66"]4/24/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02790a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Files Transferred[/TD]
[TD="class: xl65"]Printer01[/TD]
[TD="class: xl66"]4/30/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02791a_VEN[/TD]
[TD="class: xl65"]Thermal[/TD]
[TD="class: xl65"]Rejected[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]Back To Client[/TD]
[/TR]
[TR]
[TD="class: xl68"]02801a_VEN[/TD]
[TD="class: xl68"]EQP[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl68"]02802a_VEN[/TD]
[TD="class: xl68"]EQP[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl68"]02803a_VEN[/TD]
[TD="class: xl68"]EQP[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl68"]02804a_VEN[/TD]
[TD="class: xl68"]EQP[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { font-size: 10pt; font-weight: 700; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; text-align: left; }.xl67 { color: white; font-size: 10pt; text-align: left; }.xl68 { color: black; font-size: 10pt; }.xl69 { font-size: 10pt; text-align: left; }</style>

Requirements:
1. I want to copy the required details from 'OperatorDetails' sheet to 'Dashboard' sheet in a given format:

[TABLE="width: 331"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:4522;width:106pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 81"]Job Number[/TD]
[TD="class: xl64, width: 63"]Brand[/TD]
[TD="class: xl64, width: 106"]PrinterName[/TD]
[TD="class: xl64, width: 81"]Date Uploaded[/TD]
[/TR]
[TR]
[TD="class: xl65"]02789a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Printer02[/TD]
[TD="class: xl66"]4/24/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02786a_VEN[/TD]
[TD="class: xl65"]Thermal[/TD]
[TD="class: xl65"]Printer01
[/TD]
[TD="class: xl66"]4/30/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02790a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Printer01[/TD]
[TD="class: xl66"]4/30/18[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { font-size: 10pt; font-weight: 700; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; text-align: left; }</style>
As you notice, the data is sorted according to Date uploaded.

Further to add, the data needs to be populated to the Dashboard in real time without disturbing the old data. Means, that every new data needs will append below the next available empty row. i.e.

[TABLE="width: 331"]
<tbody>[TR]
[TD="class: xl64, width: 81"]02788a_VEN
[/TD]
[TD="class: xl64, width: 63"]EFS
[/TD]
[TD="class: xl64, width: 106"]Printer02
[/TD]
[TD="class: xl65, width: 81"]5/2/18[/TD]
[/TR]
[TR]
[TD="class: xl66"]02801a_VEN
[/TD]
[TD="class: xl66"]EQP
[/TD]
[TD="class: xl67"]Printer02
[/TD]
[TD="class: xl65"]5/2/18
[/TD]
[/TR]
[TR]
[TD="class: xl66"]02802a_VEN[/TD]
[TD="class: xl66"]EQP
[/TD]
[TD="class: xl67"]Printer02
[/TD]
[TD="class: xl65"]5/2/18
[/TD]
[/TR]
[TR]
[TD="class: xl66"]02803a_VEN[/TD]
[TD="class: xl66"]EQP
[/TD]
[TD="class: xl67"]Printer02
[/TD]
[TD="class: xl65"]5/2/18
[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { color: black; font-size: 10pt; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; }.xl67 { font-size: 10pt; text-align: left; }</style>
The data above will append to the old data just after the '02790a_VEN'.

So, if you finally look at the Dashboard sheet, you'll see:
[TABLE="width: 331"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:4522;width:106pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3456;width:81pt" width="81"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 81"]Job Number[/TD]
[TD="class: xl64, width: 63"]Brand[/TD]
[TD="class: xl64, width: 106"]PrinterName[/TD]
[TD="class: xl64, width: 81"]Date Uploaded[/TD]
[/TR]
[TR]
[TD="class: xl65"]02789a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Printer02[/TD]
[TD="class: xl66"]4/24/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02786a_VEN[/TD]
[TD="class: xl65"]Thermal[/TD]
[TD="class: xl65"]Printer01[/TD]
[TD="class: xl66"]4/30/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02790a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Printer01[/TD]
[TD="class: xl66"]4/30/18[/TD]
[/TR]
[TR]
[TD="class: xl65"]02788a_VEN[/TD]
[TD="class: xl65"]EFS[/TD]
[TD="class: xl65"]Printer02[/TD]
[TD="class: xl66"]5/2/18[/TD]
[/TR]
[TR]
[TD="class: xl67"]02801a_VEN[/TD]
[TD="class: xl67"]EQP[/TD]
[TD="class: xl68"]Printer02[/TD]
[TD="class: xl66"]5/2/18[/TD]
[/TR]
[TR]
[TD="class: xl67"]02802a_VEN[/TD]
[TD="class: xl67"]EQP[/TD]
[TD="class: xl68"]Printer02[/TD]
[TD="class: xl66"]5/2/18[/TD]
[/TR]
[TR]
[TD="class: xl67"]02803a_VEN[/TD]
[TD="class: xl67"]EQP[/TD]
[TD="class: xl68"]Printer02[/TD]
[TD="class: xl66"]5/2/18[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; }.xl64 { font-size: 10pt; font-weight: 700; text-align: left; }.xl65 { color: black; font-size: 10pt; text-align: left; }.xl66 { color: black; font-size: 10pt; text-align: left; }.xl67 { color: black; font-size: 10pt; }.xl68 { font-size: 10pt; text-align: left; }</style>
Can you please suggest something…

Thanks in advance.

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Job Number[/td][td]Brand[/td][td]Status[/td][td]PrinterName[/td][td]Date Uploaded[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]02786a_VEN[/td][td]Thermal[/td][td]Files Transferred[/td][td]Printer01[/td][td]

4/30/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]02787a_VEN[/td][td]EQP[/td][td]Rejected[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]02788a_VEN[/td][td]EFS[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]02789a_VEN[/td][td]EFS[/td][td]Files Transferred[/td][td]Printer02[/td][td]

4/24/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]02790a_VEN[/td][td]EFS[/td][td]Files Transferred[/td][td]Printer01[/td][td]

4/30/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]02791a_VEN[/td][td]Thermal[/td][td]Rejected[/td][td][/td][td]Back To Client[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]02801a_VEN[/td][td]EQP[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]02802a_VEN[/td][td]EQP[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]02803a_VEN[/td][td]EQP[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]02804a_VEN[/td][td]EQP[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Job Number[/td][td]Brand[/td][td]PrinterName[/td][td]Date Uploaded[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]02789a_VEN[/td][td]EFS[/td][td]Printer02[/td][td]

4/24/2018​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]02786a_VEN[/td][td]Thermal[/td][td]Printer01[/td][td]

4/30/2018​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]02790a_VEN[/td][td]EFS[/td][td]Printer01[/td][td]

4/30/2018​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet28[/td][/tr][/table]

Formula in D14:
=SMALL($E$2:$E$11,ROWS($A$1:A1))

Array formula in cell A14:
=INDEX($A$2:$D$11,SMALL(IF($D14=$E$2:$E$11,MATCH(ROW($E$2:$E$11),ROW($E$2:$E$11)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS($A$1:A1))
Copy cell A14 and paste to the next cell to the right, then copy both cells A14:B14 and paste to cells below as far as needed.

Array formula in cell C14:
=INDEX($A$2:$D$11,SMALL(IF($D14=$E$2:$E$11,MATCH(ROW($E$2:$E$11),ROW($E$2:$E$11)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS($A$1:D1))
Copy cell C14 and paste to cells below as far as needed.

 

[MASOODAHMAD]

Thanks Oscar, it really worked.

Since my data is in two different files, I changed the references accordingly:

Formula in D14:
=SMALL([v4.xlsx]Printer_Sheet!$F$2:$F5141,ROWS([v4.xlsx]Printer_Sheet!$A$1:A1))

Array Formula in Cell A14:
=INDEX([v4.xlsx]Printer_Sheet!$A$2:$M5141,SMALL(IF($D14=[v4.xlsx]Printer_Sheet!$F$2:$F5141,MATCH(ROW([v4.xlsx]Printer_Sheet!$F$2:$F5141),ROW([v4.xlsx]Printer_Sheet!$F$2:$F5141)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS([v4.xlsx]Printer_Sheet!$A$1:A1))

And so in B14 and C14.

What I figured out now is that it is giving me "#NUM!" error for blank rows. So I need to get rid of these.
Secondly, I want to fetch the data only when the value in C column of my source is equal to "Files Transferred".

The formula worked well on my MS Excel on Mac. I wonder if the same works too on Google sheets?

One again thanks a lot for your time and suggestions.

 

[Oscar Cronquist]

What I figured out now is that it is giving me "#NUM!" error for blank rows. So I need to get rid of these.
Secondly, I want to fetch the data only when the value in C column of my source is equal to "Files Transferred".

Array formula in cell A14:
=IF(ROWS($A$1:A1)>COUNT($E$2:$E$11),"",INDEX($A$2:$D$11,SMALL(IF(($D14=$E$2:$E$11)*($C$2:$C$11="Files Transferred"),MATCH(ROW($E$2:$E$11),ROW($E$2:$E$11)),""),COUNTIF($D$14:$D14,$D14)),COLUMNS($A$1:A1)))

 

[MASOODAHMAD]

Thanks Oscar, that really worked. And sorry for thanking you so late as I got stuck in some other work.